Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Vlookup from 2 different sheets
I would like my vlookup function to look for a value in one sheet and if it
doesn't find it, look in another sheet. Is this possible? I was considering using a nested if function but not sure how. |
#2
|
|||
|
|||
=IF(ISNA(vlookup_table1),vlookup_table2,vlookup_ta ble1)
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "WorkerBee" wrote in message ... I would like my vlookup function to look for a value in one sheet and if it doesn't find it, look in another sheet. Is this possible? I was considering using a nested if function but not sure how. |
#3
|
|||
|
|||
=IF(ISNA(vlookup_table1),vlookup_table2,vlookup_ta ble1)
-- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "WorkerBee" wrote in message ... I would like my vlookup function to look for a value in one sheet and if it doesn't find it, look in another sheet. Is this possible? I was considering using a nested if function but not sure how. |
#4
|
|||
|
|||
Hi!
Assume lookup value is in sheet1 A1. The first lookup table is in sheet2 A1:B5. The second lookup table is in sheet3 A1:B5. =IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B5,2,0)),IF(ISERR OR (VLOOKUP(A1,Sheet3!A1:B5,2,0)),"",VLOOKUP(A1,Sheet 3! A1:B5,2,0)),VLOOKUP(A1,Sheet2!A1:B5,2,0)) Biff -----Original Message----- I would like my vlookup function to look for a value in one sheet and if it doesn't find it, look in another sheet. Is this possible? I was considering using a nested if function but not sure how. . |
#5
|
|||
|
|||
Say the value to lookup is in D1 of Sheet1.
The formula is in Sheet1. The datalist on Sheet1 is in A1 to C100. The datalist on Sheet2 is in K1 to M50. The list on Sheet1 has the return value in Column C, The list on Sheet2 has the return value in Column L. The datalist on Sheet1 has preference, meaning it will be the first list searched, and if matches exist in both lists, the return will come from Sheet1. =IF(ISNA(MATCH(D1,A1:A100,0)),VLOOKUP(D1,SHEET2!K1 :M50,2,0),VLOOKUP(D1,A1:C1 00,3,0)) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- Try this in Sheet1: "WorkerBee" wrote in message ... I would like my vlookup function to look for a value in one sheet and if it doesn't find it, look in another sheet. Is this possible? I was considering using a nested if function but not sure how. |
#6
|
|||
|
|||
Hi Biff - ISERROR is a very general error trap in a VLOOKUP, and is usually
better served with ISNA which will trap just the specific issue the OP wants to cater for. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------------------------- It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------------------------- "Biff" wrote in message ... Hi! Assume lookup value is in sheet1 A1. The first lookup table is in sheet2 A1:B5. The second lookup table is in sheet3 A1:B5. =IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B5,2,0)),IF(ISERR OR (VLOOKUP(A1,Sheet3!A1:B5,2,0)),"",VLOOKUP(A1,Sheet 3! A1:B5,2,0)),VLOOKUP(A1,Sheet2!A1:B5,2,0)) Biff -----Original Message----- I would like my vlookup function to look for a value in one sheet and if it doesn't find it, look in another sheet. Is this possible? I was considering using a nested if function but not sure how. . |
#7
|
|||
|
|||
.... and using 0 for the Range_lookup argument seems a bit perverse as
against FALSE. -- HTH RP (remove nothere from the email address if mailing direct) "Ken Wright" wrote in message ... Hi Biff - ISERROR is a very general error trap in a VLOOKUP, and is usually better served with ISNA which will trap just the specific issue the OP wants to cater for. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "Biff" wrote in message ... Hi! Assume lookup value is in sheet1 A1. The first lookup table is in sheet2 A1:B5. The second lookup table is in sheet3 A1:B5. =IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B5,2,0)),IF(ISERR OR (VLOOKUP(A1,Sheet3!A1:B5,2,0)),"",VLOOKUP(A1,Sheet 3! A1:B5,2,0)),VLOOKUP(A1,Sheet2!A1:B5,2,0)) Biff -----Original Message----- I would like my vlookup function to look for a value in one sheet and if it doesn't find it, look in another sheet. Is this possible? I was considering using a nested if function but not sure how. . |
#8
|
|||
|
|||
LOL - But I'm a lazy typist too and almost invariably use that syntax. :-)
-- Regards Ken....................... "Bob Phillips" wrote in message ... ... and using 0 for the Range_lookup argument seems a bit perverse as against FALSE. -- HTH RP (remove nothere from the email address if mailing direct) "Ken Wright" wrote in message ... Hi Biff - ISERROR is a very general error trap in a VLOOKUP, and is usually better served with ISNA which will trap just the specific issue the OP wants to cater for. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "Biff" wrote in message ... Hi! Assume lookup value is in sheet1 A1. The first lookup table is in sheet2 A1:B5. The second lookup table is in sheet3 A1:B5. =IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B5,2,0)),IF(ISERR OR (VLOOKUP(A1,Sheet3!A1:B5,2,0)),"",VLOOKUP(A1,Sheet 3! A1:B5,2,0)),VLOOKUP(A1,Sheet2!A1:B5,2,0)) Biff -----Original Message----- I would like my vlookup function to look for a value in one sheet and if it doesn't find it, look in another sheet. Is this possible? I was considering using a nested if function but not sure how. . |
#9
|
|||
|
|||
Needs FALSE at the end Ken to force the #N/A.
-- HTH RP (remove nothere from the email address if mailing direct) "Ken Wright" wrote in message ... =IF(ISNA(vlookup_table1),vlookup_table2,vlookup_ta ble1) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "WorkerBee" wrote in message ... I would like my vlookup function to look for a value in one sheet and if it doesn't find it, look in another sheet. Is this possible? I was considering using a nested if function but not sure how. |
#10
|
|||
|
|||
Hi Bob, good catch - I assumed he would have that in his vlookup_table1 &
vlookup_table2, but my assumptions aren't always that good :-) -- Regards Ken....................... "Bob Phillips" wrote in message ... Needs FALSE at the end Ken to force the #N/A. -- HTH RP (remove nothere from the email address if mailing direct) "Ken Wright" wrote in message ... =IF(ISNA(vlookup_table1),vlookup_table2,vlookup_ta ble1) -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "WorkerBee" wrote in message ... I would like my vlookup function to look for a value in one sheet and if it doesn't find it, look in another sheet. Is this possible? I was considering using a nested if function but not sure how. |
#11
|
|||
|
|||
You surprise me sir (not the lazy bit <vbg).
I always go for clarity over brevity <ebg Bob "Ken Wright" wrote in message ... LOL - But I'm a lazy typist too and almost invariably use that syntax. :-) -- Regards Ken....................... "Bob Phillips" wrote in message ... ... and using 0 for the Range_lookup argument seems a bit perverse as against FALSE. -- HTH RP (remove nothere from the email address if mailing direct) "Ken Wright" wrote in message ... Hi Biff - ISERROR is a very general error trap in a VLOOKUP, and is usually better served with ISNA which will trap just the specific issue the OP wants to cater for. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "Biff" wrote in message ... Hi! Assume lookup value is in sheet1 A1. The first lookup table is in sheet2 A1:B5. The second lookup table is in sheet3 A1:B5. =IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B5,2,0)),IF(ISERR OR (VLOOKUP(A1,Sheet3!A1:B5,2,0)),"",VLOOKUP(A1,Sheet 3! A1:B5,2,0)),VLOOKUP(A1,Sheet2!A1:B5,2,0)) Biff -----Original Message----- I would like my vlookup function to look for a value in one sheet and if it doesn't find it, look in another sheet. Is this possible? I was considering using a nested if function but not sure how. . |
#12
|
|||
|
|||
Laziness Clarity Brevity
-- Regards Ken....................... "Bob Phillips" wrote in message ... You surprise me sir (not the lazy bit <vbg). I always go for clarity over brevity <ebg Bob "Ken Wright" wrote in message ... LOL - But I'm a lazy typist too and almost invariably use that syntax. :-) -- Regards Ken....................... "Bob Phillips" wrote in message ... ... and using 0 for the Range_lookup argument seems a bit perverse as against FALSE. -- HTH RP (remove nothere from the email address if mailing direct) "Ken Wright" wrote in message ... Hi Biff - ISERROR is a very general error trap in a VLOOKUP, and is usually better served with ISNA which will trap just the specific issue the OP wants to cater for. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 -------------------------------------------------------------------------- -- It's easier to beg forgiveness than ask permission :-) -------------------------------------------------------------------------- -- "Biff" wrote in message ... Hi! Assume lookup value is in sheet1 A1. The first lookup table is in sheet2 A1:B5. The second lookup table is in sheet3 A1:B5. =IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B5,2,0)),IF(ISERR OR (VLOOKUP(A1,Sheet3!A1:B5,2,0)),"",VLOOKUP(A1,Sheet 3! A1:B5,2,0)),VLOOKUP(A1,Sheet2!A1:B5,2,0)) Biff -----Original Message----- I would like my vlookup function to look for a value in one sheet and if it doesn't find it, look in another sheet. Is this possible? I was considering using a nested if function but not sure how. . |
#13
|
|||
|
|||
Hi Ken!
Yes, I know ISERROR is the "catch-all". Just wondering why you and RD didn't include an error trap for the second lookup? FALSE or 0? sheesh! Wouldn't that be the same as: =CONCATENATE(A1,B1) or =A1&B1 Biff -----Original Message----- Hi Biff - ISERROR is a very general error trap in a VLOOKUP, and is usually better served with ISNA which will trap just the specific issue the OP wants to cater for. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------- ------------------ It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------- ------------------ "Biff" wrote in message ... Hi! Assume lookup value is in sheet1 A1. The first lookup table is in sheet2 A1:B5. The second lookup table is in sheet3 A1:B5. =IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B5,2,0)),IF(ISERR OR (VLOOKUP(A1,Sheet3!A1:B5,2,0)),"",VLOOKUP(A1,Sheet 3! A1:B5,2,0)),VLOOKUP(A1,Sheet2!A1:B5,2,0)) Biff -----Original Message----- I would like my vlookup function to look for a value in one sheet and if it doesn't find it, look in another sheet. Is this possible? I was considering using a nested if function but not sure how. . . |
#14
|
|||
|
|||
I usually wait for the OP to post back, requesting a way to eliminate the
#N/A! That way I have time to get up and fix another drink! Where I am, it WAS SATURDAY EVENING !!!<g -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Biff" wrote in message ... Hi Ken! Yes, I know ISERROR is the "catch-all". Just wondering why you and RD didn't include an error trap for the second lookup? FALSE or 0? sheesh! Wouldn't that be the same as: =CONCATENATE(A1,B1) or =A1&B1 Biff -----Original Message----- Hi Biff - ISERROR is a very general error trap in a VLOOKUP, and is usually better served with ISNA which will trap just the specific issue the OP wants to cater for. -- Regards Ken....................... Microsoft MVP - Excel Sys Spec - Win XP Pro / XL 97/00/02/03 ---------------------------------------------------------- ------------------ It's easier to beg forgiveness than ask permission :-) ---------------------------------------------------------- ------------------ "Biff" wrote in message ... Hi! Assume lookup value is in sheet1 A1. The first lookup table is in sheet2 A1:B5. The second lookup table is in sheet3 A1:B5. =IF(ISERROR(VLOOKUP(A1,Sheet2!A1:B5,2,0)),IF(ISERR OR (VLOOKUP(A1,Sheet3!A1:B5,2,0)),"",VLOOKUP(A1,Sheet 3! A1:B5,2,0)),VLOOKUP(A1,Sheet2!A1:B5,2,0)) Biff -----Original Message----- I would like my vlookup function to look for a value in one sheet and if it doesn't find it, look in another sheet. Is this possible? I was considering using a nested if function but not sure how. . . |
#15
|
|||
|
|||
LOL - Who needs the second trap? #NA is just as good an indicator as
anything I could chuck in, and assuming the OP had used that last argument of 0 in each of his VLOOKUPs then the formula will return an error if the value doesn't exist in the second table either. -- Regards Ken....................... "Biff" wrote in message ... Hi Ken! Yes, I know ISERROR is the "catch-all". Just wondering why you and RD didn't include an error trap for the second lookup? FALSE or 0? sheesh! Wouldn't that be the same as: =CONCATENATE(A1,B1) or =A1&B1 Biff <snip |
#16
|
|||
|
|||
If MS ever implements the additional optional argement for lookup
functions I argued for since eons... =VLOOKUP(LookupValue,TableX,ColIdx,0,VLOOKUP(Looku pValue,TableY,ColIdx,0,ReturnValue) where ReturnValue is optional, which if omitted defaults to #N/A. Looks nice, no? Ken Wright wrote: LOL - Who needs the second trap? #NA is just as good an indicator as anything I could chuck in, and assuming the OP had used that last argument of 0 in each of his VLOOKUPs then the formula will return an error if the value doesn't exist in the second table either. |
#17
|
|||
|
|||
Works for me :-)
-- Regards Ken....................... "Aladin Akyurek" wrote in message ... If MS ever implements the additional optional argement for lookup functions I argued for since eons... =VLOOKUP(LookupValue,TableX,ColIdx,0,VLOOKUP(Looku pValue,TableY,ColIdx,0,Ret urnValue) where ReturnValue is optional, which if omitted defaults to #N/A. Looks nice, no? Ken Wright wrote: LOL - Who needs the second trap? #NA is just as good an indicator as anything I could chuck in, and assuming the OP had used that last argument of 0 in each of his VLOOKUPs then the formula will return an error if the value doesn't exist in the second table either. |
#18
|
|||
|
|||
Hey Aladin,
If Ken was Bill Gates and I was Steve Ballmer, believe me ... You'd have your wish! But since we're not ... Dream On.<g -- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Aladin Akyurek" wrote in message ... If MS ever implements the additional optional argement for lookup functions I argued for since eons... =VLOOKUP(LookupValue,TableX,ColIdx,0,VLOOKUP(Looku pValue,TableY,ColIdx,0,Ret urnValue) where ReturnValue is optional, which if omitted defaults to #N/A. Looks nice, no? Ken Wright wrote: LOL - Who needs the second trap? #NA is just as good an indicator as anything I could chuck in, and assuming the OP had used that last argument of 0 in each of his VLOOKUPs then the formula will return an error if the value doesn't exist in the second table either. |
#19
|
|||
|
|||
Never say Never, you might be surprised ;-)
-- Regards Ken....................... "Ragdyer" wrote in message ... Hey Aladin, If Ken was Bill Gates and I was Steve Ballmer, believe me ... You'd have your wish! But since we're not ... Dream On.<g -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Aladin Akyurek" wrote in message ... If MS ever implements the additional optional argement for lookup functions I argued for since eons... =VLOOKUP(LookupValue,TableX,ColIdx,0,VLOOKUP(Looku pValue,TableY,ColIdx,0,Ret urnValue) where ReturnValue is optional, which if omitted defaults to #N/A. Looks nice, no? Ken Wright wrote: LOL - Who needs the second trap? #NA is just as good an indicator as anything I could chuck in, and assuming the OP had used that last argument of 0 in each of his VLOOKUPs then the formula will return an error if the value doesn't exist in the second table either. |
#20
|
|||
|
|||
Excuse me sir, BUT ... exactly where did I say "Never" ?<g
-- Regards, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "Ken Wright" wrote in message ... Never say Never, you might be surprised ;-) -- Regards Ken....................... "Ragdyer" wrote in message ... Hey Aladin, If Ken was Bill Gates and I was Steve Ballmer, believe me ... You'd have your wish! But since we're not ... Dream On.<g -- Regards, RD -------------------------------------------------------------------------- - Please keep all correspondence within the NewsGroup, so all may benefit ! -------------------------------------------------------------------------- - "Aladin Akyurek" wrote in message ... If MS ever implements the additional optional argement for lookup functions I argued for since eons... =VLOOKUP(LookupValue,TableX,ColIdx,0,VLOOKUP(Looku pValue,TableY,ColIdx,0,Ret urnValue) where ReturnValue is optional, which if omitted defaults to #N/A. Looks nice, no? Ken Wright wrote: LOL - Who needs the second trap? #NA is just as good an indicator as anything I could chuck in, and assuming the OP had used that last argument of 0 in each of his VLOOKUPs then the formula will return an error if the value doesn't exist in the second table either. |
#21
|
|||
|
|||
LOL :-)
-- Regards Ken....................... <snip |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copying multiple sheets from one book 2 another and undertake spec | Excel Discussion (Misc queries) | |||
Sheets Skip Macro | Excel Discussion (Misc queries) | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
VLOOKUP across all sheets in a workbook | Excel Worksheet Functions | |||
Multiple sheets selected | Excel Discussion (Misc queries) |