VLookUp
 

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

Thanks in advance for you help.

T

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(TRU E,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

"TPratt" wrote in message
...
Does anyone know how one could use a vlookup to look across multiple tabs
within a file?

Thanks in advance for you help.

T

Thanks Biff

It looks very clean. We'll give it a try.

"Biff" wrote in message
...
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(TRU E,COUNTIF(INDIRECT("'"&She
etList&"'!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

"TPratt" wrote in message
...
Does anyone know how one could use a vlookup to look across multiple
tabs
within a file?

Thanks in advance for you help.

T

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

"TPratt" wrote in message
...
Thanks Biff

It looks very clean. We'll give it a try.

"Biff" wrote in message
...
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(TRU E,COUNTIF(INDIRECT("'"&She
etList&"'!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

"TPratt" wrote in message
...
Does anyone know how one could use a vlookup to look across multiple
tabs
within a file?

Thanks in advance for you help.

T