Looking up against a variable named range??

Originally Posted by jghutch

Hi

For a while now I've not been able to write a formula that looks up against a variable named range - I need some help!

For example, say I have 2 named ranges, Area1 and Area2, both 5x5. I also have an input cell, A1, and an output cell, B1

What I want to be able to do is for the user to type in to cell A1 the range they want to look up against (eg "Area2") and then for the output cell (B1) to return a certain cell in that range (eg 2nd row and 3rd column)

I've tried VLOOKUP, INDEX and others but they all seem to need a fixed range to look up aganist, and cant take a variable argument.

Any help much appreciated!

Thanks

I think the attached will show what you want, H2 being the series, I2 and J2 the table lookups, drop the formula at H4 into the cell you want the look'd up value to appear.

hth
---

note, there are no validity checks in this yet.

Originally Posted by jghutch

Thanks for that Bryan. That certianly helps. However in reality I may have up to around 50 named ranges as options to chose from. Is there anyway I can do this without using a monster sized IF function at the beginning of the INDEX(...) part??

Thanks

=INDEX(INDIRECT(VLOOKUP(H2,K2:L3,2,FALSE)),MATCH(I2,A2:A6,0),MATCH(J2,B1:F1,0))


where K2:k3 contain 1 and 2, and L2:L3 contain B2:F6 and B9:F13 (ie a table of series)
---

Originally Posted by jghutch

Ahhh! That sneaky INDIRECT formula - I didn't know it existed, but it does seem very powerful. You have solved something that I thought was impossible!

Thanks very much Bryan

You can also use the Index, as

=Index((A,b,c,d,e,f,g),row,col,4)

or
=Index((A1:B3,C6:D8,F9:G11,A15:B17),row,col,4)

to index fron range name d, or area A15:B17, however in the case stated the Indirect VLookup seems the better option.

---