Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple ranges on Vlookup
I currently have my Vlookup stmnt as this:
=IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) 'Code Decrip' is the name of the worksheet I need to add another range X$3:Y48 What is the proper syntax - I wasnt able to get it right after searching online for it. Thanks so much. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple ranges on Vlookup
Hi lpj
Not quite clear what you want to achieve here? Why not have everything in the same range? Am I correct in guessing that, if you do not find a matching record in the primary range, you then want to do a lookup in the secondary range? If so, you will have to use an If(OR( statement, to first look at the primary range, and then, if you do not find anything there, do a VLOOKUP in the secondary range. You will therefore have to test the primary range for an error condition, and if the error condition exists, then look at the secondary range, else look at the primary range. Again, why not put the whole lot in one range? -- ve_2nd_at. Stilfontein, Northwest, South Africa "lpj" wrote: I currently have my Vlookup stmnt as this: =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) 'Code Decrip' is the name of the worksheet I need to add another range X$3:Y48 What is the proper syntax - I wasnt able to get it right after searching online for it. Thanks so much. |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple ranges on Vlookup
Thanks for the reply! The reason I can't put it all in the same range is bc
this is an existing worksheet (which can't be modified) and there are some columns of data in btwn that shouldn't be referrenced - they could create duplicates or give invalid results back. "Kassie" wrote: Hi lpj Not quite clear what you want to achieve here? Why not have everything in the same range? Am I correct in guessing that, if you do not find a matching record in the primary range, you then want to do a lookup in the secondary range? If so, you will have to use an If(OR( statement, to first look at the primary range, and then, if you do not find anything there, do a VLOOKUP in the secondary range. You will therefore have to test the primary range for an error condition, and if the error condition exists, then look at the secondary range, else look at the primary range. Again, why not put the whole lot in one range? -- ve_2nd_at. Stilfontein, Northwest, South Africa "lpj" wrote: I currently have my Vlookup stmnt as this: =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) 'Code Decrip' is the name of the worksheet I need to add another range X$3:Y48 What is the proper syntax - I wasnt able to get it right after searching online for it. Thanks so much. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple ranges on Vlookup
Thanks so much - to all of you! I really appreciate it! :)
"Kassie" wrote: Hi lpj Try the following formula: =IF(E2="","",IF(ISERROR(VLOOKUP('Code Descrip'!T$3:U$27,0,FALSE)),VLOOKUP(E2,'Code Descrip'!X$3:Y$48,2,FALSE),VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) -- ve_2nd_at. Stilfontein, Northwest, South Africa "lpj" wrote: Thanks for the reply! The reason I can't put it all in the same range is bc this is an existing worksheet (which can't be modified) and there are some columns of data in btwn that shouldn't be referrenced - they could create duplicates or give invalid results back. "Kassie" wrote: Hi lpj Not quite clear what you want to achieve here? Why not have everything in the same range? Am I correct in guessing that, if you do not find a matching record in the primary range, you then want to do a lookup in the secondary range? If so, you will have to use an If(OR( statement, to first look at the primary range, and then, if you do not find anything there, do a VLOOKUP in the secondary range. You will therefore have to test the primary range for an error condition, and if the error condition exists, then look at the secondary range, else look at the primary range. Again, why not put the whole lot in one range? -- ve_2nd_at. Stilfontein, Northwest, South Africa "lpj" wrote: I currently have my Vlookup stmnt as this: =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) 'Code Decrip' is the name of the worksheet I need to add another range X$3:Y48 What is the proper syntax - I wasnt able to get it right after searching online for it. Thanks so much. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple ranges on Vlookup
How would i nest another 1 or 2 ranges in there? I'm looking at this syntax
but not getting very far - can i use 'or' operators? thanks again for your help! "Kassie" wrote: Hi lpj Try the following formula: =IF(E2="","",IF(ISERROR(VLOOKUP('Code Descrip'!T$3:U$27,0,FALSE)),VLOOKUP(E2,'Code Descrip'!X$3:Y$48,2,FALSE),VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) -- ve_2nd_at. Stilfontein, Northwest, South Africa "lpj" wrote: Thanks for the reply! The reason I can't put it all in the same range is bc this is an existing worksheet (which can't be modified) and there are some columns of data in btwn that shouldn't be referrenced - they could create duplicates or give invalid results back. "Kassie" wrote: Hi lpj Not quite clear what you want to achieve here? Why not have everything in the same range? Am I correct in guessing that, if you do not find a matching record in the primary range, you then want to do a lookup in the secondary range? If so, you will have to use an If(OR( statement, to first look at the primary range, and then, if you do not find anything there, do a VLOOKUP in the secondary range. You will therefore have to test the primary range for an error condition, and if the error condition exists, then look at the secondary range, else look at the primary range. Again, why not put the whole lot in one range? -- ve_2nd_at. Stilfontein, Northwest, South Africa "lpj" wrote: I currently have my Vlookup stmnt as this: =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) 'Code Decrip' is the name of the worksheet I need to add another range X$3:Y48 What is the proper syntax - I wasnt able to get it right after searching online for it. Thanks so much. |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple ranges on Vlookup
Assuming that T3:U27, X3:Y48, AB3:AC27, and AF3:AG48 contain your
tables, try... =IF(E2<"",VLOOKUP(E2,CHOOSE(MATCH(TRUE,COUNTIF(OF FSET(T3:U27,,{0,4,8,12} ,{25,46,25,46}),E2)0,0),T3:U27,X3:Y48,AB3:AC27,AF 3:AG48),2,0),"") ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER. Note that the first array constant... {0,4,8,12} ....determines the number of columns to move right from Column T for each table, and the second array constant... {25,46,25,46} ....determines the number of rows contained in each table. Change these accordingly. Post back if you need further help... In article , lpj wrote: How would i nest another 1 or 2 ranges in there? I'm looking at this syntax but not getting very far - can i use 'or' operators? thanks again for your help! "Kassie" wrote: Hi lpj Try the following formula: =IF(E2="","",IF(ISERROR(VLOOKUP('Code Descrip'!T$3:U$27,0,FALSE)),VLOOKUP(E2,'Code Descrip'!X$3:Y$48,2,FALSE),VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) -- ve_2nd_at. Stilfontein, Northwest, South Africa "lpj" wrote: Thanks for the reply! The reason I can't put it all in the same range is bc this is an existing worksheet (which can't be modified) and there are some columns of data in btwn that shouldn't be referrenced - they could create duplicates or give invalid results back. "Kassie" wrote: Hi lpj Not quite clear what you want to achieve here? Why not have everything in the same range? Am I correct in guessing that, if you do not find a matching record in the primary range, you then want to do a lookup in the secondary range? If so, you will have to use an If(OR( statement, to first look at the primary range, and then, if you do not find anything there, do a VLOOKUP in the secondary range. You will therefore have to test the primary range for an error condition, and if the error condition exists, then look at the secondary range, else look at the primary range. Again, why not put the whole lot in one range? -- ve_2nd_at. Stilfontein, Northwest, South Africa "lpj" wrote: I currently have my Vlookup stmnt as this: =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) 'Code Decrip' is the name of the worksheet I need to add another range X$3:Y48 What is the proper syntax - I wasnt able to get it right after searching online for it. Thanks so much. |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple ranges on Vlookup
Duplicates or invalid values - to say the value of the lookup field could
exist in 2 cells (i.e. T3 and V3, I would only want the returned value for T3 NOT V3). that's why i can't have an one entire range :) "Kassie" wrote: Hi lpj Not quite clear what you want to achieve here? Why not have everything in the same range? Am I correct in guessing that, if you do not find a matching record in the primary range, you then want to do a lookup in the secondary range? If so, you will have to use an If(OR( statement, to first look at the primary range, and then, if you do not find anything there, do a VLOOKUP in the secondary range. You will therefore have to test the primary range for an error condition, and if the error condition exists, then look at the secondary range, else look at the primary range. Again, why not put the whole lot in one range? -- ve_2nd_at. Stilfontein, Northwest, South Africa "lpj" wrote: I currently have my Vlookup stmnt as this: =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) 'Code Decrip' is the name of the worksheet I need to add another range X$3:Y48 What is the proper syntax - I wasnt able to get it right after searching online for it. Thanks so much. |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple ranges on Vlookup
Try...
=IF(E2<"",VLOOKUP(E2,IF(ISNUMBER(MATCH(E2,'Code Descrip'!T3:T27,0)),'Code Descrip'!T3:U27,'Code Descrip'!X3:Y48),2,0),"") Hope this helps! In article , lpj wrote: I currently have my Vlookup stmnt as this: =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) 'Code Decrip' is the name of the worksheet I need to add another range X$3:Y48 What is the proper syntax - I wasnt able to get it right after searching online for it. Thanks so much. |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple ranges on Vlookup
Thanks so much - to all of you! I really appreciate it! :)
"Domenic" wrote: Try... =IF(E2<"",VLOOKUP(E2,IF(ISNUMBER(MATCH(E2,'Code Descrip'!T3:T27,0)),'Code Descrip'!T3:U27,'Code Descrip'!X3:Y48),2,0),"") Hope this helps! In article , lpj wrote: I currently have my Vlookup stmnt as this: =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) 'Code Decrip' is the name of the worksheet I need to add another range X$3:Y48 What is the proper syntax - I wasnt able to get it right after searching online for it. Thanks so much. |
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple ranges on Vlookup
Hi Kassis,
I this a typo on the first line? =IF(E2="","",IF(ISERROR(VLOOKUP('Code Descrip'!T$3:U$27,0,FALSE)),VLOOKUP(E2,'Code Descrip'!X$3:Y$48,2,FALSE),VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) Should be =IF(E2="","",IF(ISERROR(VLOOKUP(E2,'Code etc... Regards, Howard "lpj" wrote in message ... I currently have my Vlookup stmnt as this: =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) 'Code Decrip' is the name of the worksheet I need to add another range X$3:Y48 What is the proper syntax - I wasnt able to get it right after searching online for it. Thanks so much. |
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
multiple ranges on Vlookup
Thanks so much - to all of you! I really appreciate it! :)
"L. Howard Kittle" wrote: Hi Kassis, I this a typo on the first line? =IF(E2="","",IF(ISERROR(VLOOKUP('Code Descrip'!T$3:U$27,0,FALSE)),VLOOKUP(E2,'Code Descrip'!X$3:Y$48,2,FALSE),VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) Should be =IF(E2="","",IF(ISERROR(VLOOKUP(E2,'Code etc... Regards, Howard "lpj" wrote in message ... I currently have my Vlookup stmnt as this: =IF(E2="","",VLOOKUP(E2,'Code Descrip'!T$3:U$27,2,FALSE)) 'Code Decrip' is the name of the worksheet I need to add another range X$3:Y48 What is the proper syntax - I wasnt able to get it right after searching online for it. Thanks so much. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Multiple if or multiple vlookup | Excel Worksheet Functions | |||
Multiple Ranges for a Chart | Charts and Charting in Excel | |||
how do I use vlookup for multiple occurrences of the same value | Excel Worksheet Functions | |||
Can VLOOKUP return multiple answers based on several identical lo. | Excel Worksheet Functions | |||
compare unique identifiers in multiple ranges | Charts and Charting in Excel |