Excel Look Up Tables - Make The Table Dynamic?
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<attarkon.ee )
"T. Valko" wrote in message
...
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_numbe r,range_lookup)
Will not work with a dynamic range and therefore a different formula would
be needed.
Biff
"Arvi Laanemets" wrote in message
...
Hi
"T. Valko" wrote in message
...
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),Ran ge1,Range2,Range3,...)
--
Arvi Laanemets
( My real mail address: arvi.laanemets<attarkon.ee )
|