MrExcel Publishing
Your One Stop for Excel Tips & Solutions

VLOOKUP - ARRGHHHH!!!!


Posted by G. Hemsworth on January 03, 2002 7:41 AM

This is hard to explain but here goes, a shortened example of my problem

I have created a small table with items 1, 2 & 3 in it with a cost to the right of each. I have Range Named this table TBLA.
I now create another table with the same but Range Name this TBLB
These are my two lookup tables

I now create a third table which has three columns:-

1. I input here, TBLA or TABLB
2. I Input here, 1, 2 or 3
3. Resultant Cost <automatic using vlookup>

My Vlookup in column 3 is as follows:-
=VLOOKUP(B3,TBLA,2) &LT;B3 being my item no.in Col 2>
This works fine but I have to type in manually which table i need the data from.
Is there anyway that the formula will look at Column1 and put in either TBLA or TBLB as it reads it.

Thanks for any advice
Frustrated


Posted by IML on January 03, 2002 7:43 AM

Try
=VLOOKUP(B3,indirect(a1),2)
where a1 is where the table name is.

Posted by bob Umlas on January 03, 2002 8:23 AM

=VLOOKUP(B3,INDIRECT(A3),2)

Posted by G. Hemsworth on January 04, 2002 1:12 AM


IT WORKS!!!!
Thanks very much IML