Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array Function with VLOOKUP | Excel Worksheet Functions | |||
make a vlookup using a variable path | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions | |||
VLOOKUP not working | Excel Worksheet Functions |