VLookUp

T

TPratt

Does anyone know how one could use a vlookup to look across multiple tabs
within a file?

Thanks in advance for you help.

T
 
B

Biff

Hi!

Try this:

Create a list of the sheet names that you want the lookup to search, say
J1:J10.

Now, create a named range that refers to that list. Name it something like
SheetList.

This will only work if the lookup range is the same on all sheets!

Assume your lookup value is in A1 and the lookup range is A:E on the other
sheets.

Entered as an array with the key combo of CTRL,SHIFT,ENTER:

=VLOOKUP(A1,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A:A"),A1)>0,0))&"'!A:E"),4,0)

This example is returning from column 4 of the lookup table.

If you want an error trap, post back!

Biff
 
B

Biff

Hi!

See your other post in the .newusers group.

I posted a shorter less complicated formula there. It helped that you posted
an example of your data there.

Biff
 
Top