Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Is it possible for VLOOKUP to work with a 3D range? - say Sheet1!A2:Sheet5!B100
I can't get it to work, but it may just be a matter of my syntax. Or is there another way of looking for something in multiple lists? Regards - Dave. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
On Sun, 30 Aug 2009 19:44:01 -0700, Dave
wrote: Is it possible for VLOOKUP to work with a 3D range? - say Sheet1!A2:Sheet5!B100 I can't get it to work, but it may just be a matter of my syntax. Or is there another way of looking for something in multiple lists? Regards - Dave. You can nest the vlookups in an if loop. If can handle up to 30 nestings. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Hi SoaSC,
So is that a "No" to 3D tables in VLookup? And when you say nesting if statements, do you mean: If(NOT(ISERR(VLOOKUP-For-Sheet1),VLOOKUP-For-Sheet1,IF(NOT(ISERR(VLOOKUP-For-Sheet2),VLOOKUP-For-Sheet2,IF(NOT(ISSERR( etc? It's really cumbersome. Are you sure there's not a nice 3D way? Dave. "Son of a Sea Cook" wrote: On Sun, 30 Aug 2009 19:44:01 -0700, Dave wrote: Is it possible for VLOOKUP to work with a 3D range? - say Sheet1!A2:Sheet5!B100 I can't get it to work, but it may just be a matter of my syntax. Or is there another way of looking for something in multiple lists? Regards - Dave. You can nest the vlookups in an if loop. If can handle up to 30 nestings. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Hi Dave
If the sheets are named Sheet1, Sheet2, Sheet3 etc; then try the below formula which will lookup the value in C1 of current sheet in 5 sheets and return the matching value...Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}". Try and feedback =VLOOKUP(C1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("Sheet"&ROW(INDIRECT("1:5"))&"!A:A"),C1)0, 0)&"!A:B"),2,0) If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: Hi SoaSC, So is that a "No" to 3D tables in VLookup? And when you say nesting if statements, do you mean: If(NOT(ISERR(VLOOKUP-For-Sheet1),VLOOKUP-For-Sheet1,IF(NOT(ISERR(VLOOKUP-For-Sheet2),VLOOKUP-For-Sheet2,IF(NOT(ISSERR( etc? It's really cumbersome. Are you sure there's not a nice 3D way? Dave. "Son of a Sea Cook" wrote: On Sun, 30 Aug 2009 19:44:01 -0700, Dave wrote: Is it possible for VLOOKUP to work with a 3D range? - say Sheet1!A2:Sheet5!B100 I can't get it to work, but it may just be a matter of my syntax. Or is there another way of looking for something in multiple lists? Regards - Dave. You can nest the vlookups in an if loop. If can handle up to 30 nestings. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Hi Jacob,
I tried this, but couldn't get it to work. It seemed to look at the other sheets ok, and found the target value, but didn't bring the result from Col 2 adjacent to that target value. Rather, it seemed to be looking in Col 2 of Sheet1. Regards - Dave. "Jacob Skaria" wrote: Hi Dave If the sheets are named Sheet1, Sheet2, Sheet3 etc; then try the below formula which will lookup the value in C1 of current sheet in 5 sheets and return the matching value...Please note that this is an array formula. Within the cell in edit mode (F2) paste this formula and press Ctrl+Shift+Enter to apply this formula. If successful in 'Formula Bar' you can notice the curly braces at both ends like "{=<formula}". Try and feedback =VLOOKUP(C1,INDIRECT("Sheet"&MATCH(TRUE,COUNTIF(IN DIRECT("Sheet"&ROW(INDIRECT("1:5"))&"!A:A"),C1)0, 0)&"!A:B"),2,0) If this post helps click Yes --------------- Jacob Skaria "Dave" wrote: Hi SoaSC, So is that a "No" to 3D tables in VLookup? And when you say nesting if statements, do you mean: If(NOT(ISERR(VLOOKUP-For-Sheet1),VLOOKUP-For-Sheet1,IF(NOT(ISERR(VLOOKUP-For-Sheet2),VLOOKUP-For-Sheet2,IF(NOT(ISSERR( etc? It's really cumbersome. Are you sure there's not a nice 3D way? Dave. "Son of a Sea Cook" wrote: On Sun, 30 Aug 2009 19:44:01 -0700, Dave wrote: Is it possible for VLOOKUP to work with a 3D range? - say Sheet1!A2:Sheet5!B100 I can't get it to work, but it may just be a matter of my syntax. Or is there another way of looking for something in multiple lists? Regards - Dave. You can nest the vlookups in an if loop. If can handle up to 30 nestings. Hi |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLookUp - Does the VLookUp return the exact information? | Excel Worksheet Functions | |||
Vlookup in vlookup - taking the result as array name | Excel Worksheet Functions | |||
Combine VLOOKUP and IF function so #NA isn't returned as a value from VLOOKUP | Excel Discussion (Misc queries) | |||
Vlookup -=VLOOKUP(F9,LookUp1!$A$2:$B$1504,2,FALSE) | New Users to Excel | |||
Vlookup info being used without vlookup table attached? | Excel Worksheet Functions |