Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookUp Tables
Hi all
I have created a VLookUp table and it works fine, however, I was wondering if it is possible to ask Excel to return two values from two columns in the look-up table rather than just one. =VLOOKUP(A1,lookup,1 and 3) Is there any way this is possible? Also, can you nest VLookUp tables?? Thank you. Louise |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookUp Tables
Hi Louise,
I'm not sure why you would want to return from column 1 of the lookup table, as that will be the same as A1 in your formula. You would need to do something like this: =VLOOKUP(A1,lookup,1,0) + VLOOKUP(A1,lookup,3,0) assuming you want an exact match. You might need to change the + to & if the returned values are text. Not sure what you mean by nesting the tables - if you have two or more tables that the lookup value could be in, then generally you would have something like: =IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),IF(ISNA(vlo okup_3),"not present",vlookup_3),vlookup_2),vlookup_1) where vlookup_n is a VLOOKUP formula referring to table n. Hope this helps. Pete Louise wrote: Hi all I have created a VLookUp table and it works fine, however, I was wondering if it is possible to ask Excel to return two values from two columns in the look-up table rather than just one. =VLOOKUP(A1,lookup,1 and 3) Is there any way this is possible? Also, can you nest VLookUp tables?? Thank you. Louise |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookUp Tables
Thanks for your reply.
I've just tried using 2 lookups as you have shown below and i get an error message, rather than it returning two values. My formula reads; =VLOOKUP(D5,test,2) + VLOOKUP(D5,test,3) what have I done wrong?? Louise "Pete_UK" wrote: Hi Louise, I'm not sure why you would want to return from column 1 of the lookup table, as that will be the same as A1 in your formula. You would need to do something like this: =VLOOKUP(A1,lookup,1,0) + VLOOKUP(A1,lookup,3,0) assuming you want an exact match. You might need to change the + to & if the returned values are text. Not sure what you mean by nesting the tables - if you have two or more tables that the lookup value could be in, then generally you would have something like: =IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),IF(ISNA(vlo okup_3),"not present",vlookup_3),vlookup_2),vlookup_1) where vlookup_n is a VLOOKUP formula referring to table n. Hope this helps. Pete Louise wrote: Hi all I have created a VLookUp table and it works fine, however, I was wondering if it is possible to ask Excel to return two values from two columns in the look-up table rather than just one. =VLOOKUP(A1,lookup,1 and 3) Is there any way this is possible? Also, can you nest VLookUp tables?? Thank you. Louise |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookUp Tables
What error message do you get?
If it is #N/A then it means that the value in D5 has not been found in test (i.e. it is less than the first value in the table). If it is #VALUE then you will need to change the + to &, as one of the returned values will be text. Please advise. Pete Louise wrote: Thanks for your reply. I've just tried using 2 lookups as you have shown below and i get an error message, rather than it returning two values. My formula reads; =VLOOKUP(D5,test,2) + VLOOKUP(D5,test,3) what have I done wrong?? Louise "Pete_UK" wrote: Hi Louise, I'm not sure why you would want to return from column 1 of the lookup table, as that will be the same as A1 in your formula. You would need to do something like this: =VLOOKUP(A1,lookup,1,0) + VLOOKUP(A1,lookup,3,0) assuming you want an exact match. You might need to change the + to & if the returned values are text. Not sure what you mean by nesting the tables - if you have two or more tables that the lookup value could be in, then generally you would have something like: =IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),IF(ISNA(vlo okup_3),"not present",vlookup_3),vlookup_2),vlookup_1) where vlookup_n is a VLOOKUP formula referring to table n. Hope this helps. Pete Louise wrote: Hi all I have created a VLookUp table and it works fine, however, I was wondering if it is possible to ask Excel to return two values from two columns in the look-up table rather than just one. =VLOOKUP(A1,lookup,1 and 3) Is there any way this is possible? Also, can you nest VLookUp tables?? Thank you. Louise |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookUp Tables
The error was #VALUE so I have changed the + to & and it worked perfectly,
thank you. I didn't know you could use two LOOKUPS at the same time. Louise. "Pete_UK" wrote: What error message do you get? If it is #N/A then it means that the value in D5 has not been found in test (i.e. it is less than the first value in the table). If it is #VALUE then you will need to change the + to &, as one of the returned values will be text. Please advise. Pete Louise wrote: Thanks for your reply. I've just tried using 2 lookups as you have shown below and i get an error message, rather than it returning two values. My formula reads; =VLOOKUP(D5,test,2) + VLOOKUP(D5,test,3) what have I done wrong?? Louise "Pete_UK" wrote: Hi Louise, I'm not sure why you would want to return from column 1 of the lookup table, as that will be the same as A1 in your formula. You would need to do something like this: =VLOOKUP(A1,lookup,1,0) + VLOOKUP(A1,lookup,3,0) assuming you want an exact match. You might need to change the + to & if the returned values are text. Not sure what you mean by nesting the tables - if you have two or more tables that the lookup value could be in, then generally you would have something like: =IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),IF(ISNA(vlo okup_3),"not present",vlookup_3),vlookup_2),vlookup_1) where vlookup_n is a VLOOKUP formula referring to table n. Hope this helps. Pete Louise wrote: Hi all I have created a VLookUp table and it works fine, however, I was wondering if it is possible to ask Excel to return two values from two columns in the look-up table rather than just one. =VLOOKUP(A1,lookup,1 and 3) Is there any way this is possible? Also, can you nest VLookUp tables?? Thank you. Louise |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLookUp Tables
Glad to be of help - thanks for feeding back. You might like to make it
&" - "& to separate the two values visually. Pete Louise wrote: The error was #VALUE so I have changed the + to & and it worked perfectly, thank you. I didn't know you could use two LOOKUPS at the same time. Louise. "Pete_UK" wrote: What error message do you get? If it is #N/A then it means that the value in D5 has not been found in test (i.e. it is less than the first value in the table). If it is #VALUE then you will need to change the + to &, as one of the returned values will be text. Please advise. Pete Louise wrote: Thanks for your reply. I've just tried using 2 lookups as you have shown below and i get an error message, rather than it returning two values. My formula reads; =VLOOKUP(D5,test,2) + VLOOKUP(D5,test,3) what have I done wrong?? Louise "Pete_UK" wrote: Hi Louise, I'm not sure why you would want to return from column 1 of the lookup table, as that will be the same as A1 in your formula. You would need to do something like this: =VLOOKUP(A1,lookup,1,0) + VLOOKUP(A1,lookup,3,0) assuming you want an exact match. You might need to change the + to & if the returned values are text. Not sure what you mean by nesting the tables - if you have two or more tables that the lookup value could be in, then generally you would have something like: =IF(ISNA(vlookup_1),IF(ISNA(vlookup_2),IF(ISNA(vlo okup_3),"not present",vlookup_3),vlookup_2),vlookup_1) where vlookup_n is a VLOOKUP formula referring to table n. Hope this helps. Pete Louise wrote: Hi all I have created a VLookUp table and it works fine, however, I was wondering if it is possible to ask Excel to return two values from two columns in the look-up table rather than just one. =VLOOKUP(A1,lookup,1 and 3) Is there any way this is possible? Also, can you nest VLookUp tables?? Thank you. Louise |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP from Access Tables | Excel Discussion (Misc queries) | |||
VLOOKUP from Access Tables | Excel Worksheet Functions | |||
Vlookup in pivot tables | Excel Worksheet Functions | |||
vlookup tables for pictures? | Excel Worksheet Functions | |||
VLookUp / 2 Tables | Excel Discussion (Misc queries) |