|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#11
February 20th, 2005, 05:31 PM
|
|||
|
|||
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
February 20th, 2005, 07:10 PM
|
|||
|
|||
|
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
February 20th, 2005, 07:29 PM
|
|||
|
|||
|
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
February 20th, 2005, 07:46 PM
|
|||
|
|||
|
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
February 20th, 2005, 08:35 PM
|
|||
|
|||
|
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
February 20th, 2005, 09:24 PM
|
|||
|
|||
|
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
February 20th, 2005, 09:38 PM
|
|||
|
|||
|
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
February 20th, 2005, 11:12 PM
|
|||
|
|||
|
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
February 21st, 2005, 12:58 AM
|
|||
|
|||
|
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
February 21st, 2005, 01:26 AM
|
|||
|
|||
|
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.
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Insert Vlookup into table_array of Vlookup with named range | Denise | Worksheet Functions | 1 | January 24th, 2005 10:49 PM |
| VLookup comparing two sheets..please | Bonny | Worksheet Functions | 2 | September 16th, 2004 01:11 PM |
| Vlookup across multiple sheets | Brian | Worksheet Functions | 6 | March 10th, 2004 11:05 PM |
Linear Mode
