Excel Look Up Tables - Make The Table Dynamic?

[Guest]

Hi.

If I have a value that I want to use to look up in a table but not
necessarily in the same table each time. Is it possible to set the Lookup
Range to be dynamic so that I can specify which table is being looked at by
changing a value in a combo box rather than having to change the formula in
the result cell?

Cheers.

 

[T. Valko]

Yes, you can do this.

How many items are in your combo box?

It'd be easier to use a data validation drop down list.

If you link the combo box to a cell the *number* of the item that is
selected is returned to the linked cell. You'd then have to parse what item
corresponds to the number.

If you use a DV drop down list you don't have to go through that extra step.
For example:

A1 = data validation drop down list. The selections are named ranges to use
in a VLOOKUP formula.

Then your lookup formula would be:

=VLOOKUP(lookup_value,INDIRECT(A1),col_index_number,range_lookup)

Biff

 

[T. Valko]

NB:

If your named ranges are *dynamic ranges* then a different formula will be
needed. If that's the case this adds that extra step back into the process
and you might as well just stick with a combo box.

Biff

 

[Arvi Laanemets]

Hi

NB:

If your named ranges are *dynamic ranges* then a different formula will be
needed. If that's the case this adds that extra step back into the process
and you might as well just stick with a combo box.

Not at all!

An example:
=CHOOSE(MATCH(A1,{Value1;Value2;Value3;...},0),Range1,Range2,Range3,...)

 

[T. Valko]

Not at all!

What I meant was:

If your named ranges are *dynamic ranges* then a different formula will be
needed.

=VLOOKUP(lookup_value,INDIRECT(A1),col_index_number,range_lookup)

Will not work with a dynamic range and therefore a different formula would
be needed.

Biff

 

[Arvi Laanemets]

Hi

To use with VLOOKUP, I'd create an additional named range, i.e.

- You have some named ranges Name1, Name2, ...
- You create a named range, p.e. LookupRange, which returns one of those,
depending on value in cell SomeSheet!$A$1
- Your lookup formula will be:
=VLOOKUP(lookup_value,LookupRange,col_index_number,range_lookup)

A bonus is, that whenever you need to change rules for selecting named
ranges, you have to change them once - for LookupRange - instead editing
every formula in workbook.



--
Arvi Laanemets
( My real mail address: arvi.laanemets<at>tarkon.ee )

Not at all!

What I meant was:

If your named ranges are *dynamic ranges* then a different formula will be
needed.

=VLOOKUP(lookup_value,INDIRECT(A1),col_index_number,range_lookup)

Will not work with a dynamic range and therefore a different formula would
be needed.

Biff