MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Vlookups


Posted by EJ on November 13, 2001 3:19 PM

I want to do a vlookup. I know that the formula is =vlookup(cell reference,a range,a column reference)

Let's say my example is =vlookup(a state,'Dave's papers'B2:h56,5)

My question has to do with "a range"
Say my range (Dave's papers) is one tab in my spreadsheet. I also have a tab called Pete's papers with the same range and column reference.

I then create a drop down box in my spreadsheet that has 'Dave's papers' and 'Pete's papers'listed on separate lines.

How come I cannot just say =vlookup(a state,the cell reference to the drop down box, and then somehow put an "&" plus the rest of the identical range for Dave/Petes range and col reference)

does someone know how I can do something like this?
Basically doing a lookup with a reference to a drop down box where the drop down box is part of the range reference(a label). The drop down box works with "a state" in my vlookup example but it isn't part of a label.
I've even tried creating ranges through insert/name/define, this works when you want to create lists for drop down boxes but you don't want that data in your actual spreadsheet you are working in.

Any ideas?


Posted by IML on November 13, 2001 3:35 PM

YOu can do this. The format would be
=VLOOKUP(A1,INDIRECT(D1&"!$A$1:$B$10"),2,FALSE)
where you are looking up A1 in the Range of A1:B10 on the sheet named identically to what is in cell d1.

good luck.

Posted by Aladin Akyurek on November 13, 2001 3:35 PM

Try:

=VLOOKUP(a-state,INDIRECT("'"&A1&"'!B2:H56"),5)

where A1 references the choice from your drop-down list.

The drop-down list should contain the sheet names

Dave's papers
etc.

Aladin