Vlookup from 2 different sheets

[WorkerBee]

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.

 

[Ken Wright]

=IF(ISNA(vlookup_table1),vlookup_table2,vlookup_table1)

 

[Ken Wright]

=IF(ISNA(vlookup_table1),vlookup_table2,vlookup_table1)

 

[Biff]

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(ISERROR
(VLOOKUP(A1,Sheet3!A1:B5,2,0)),"",VLOOKUP(A1,Sheet3!
A1:B5,2,0)),VLOOKUP(A1,Sheet2!A1:B5,2,0))

Biff

 

[Ragdyer]

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))

 

[Ken Wright]

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.

 

[Bob Phillips]

.... 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]

LOL - But I'm a lazy typist too and almost invariably use that syntax.

 

[Bob Phillips]

Needs FALSE at the end Ken to force the #N/A.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Ken Wright]

Hi Bob, good catch - I assumed he would have that in his vlookup_table1 &
vlookup_table2, but my assumptions aren't always that good

 

[Bob Phillips]

You surprise me sir (not the lazy bit <vbg>).

I always go for clarity over brevity <ebg>

Bob

LOL - But I'm a lazy typist too and almost invariably use that syntax.

--
Regards
Ken.......................

... 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)

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

--------------------------------------------------------------------------

--------------------------------------------------------------------------

--

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(ISERROR
(VLOOKUP(A1,Sheet3!A1:B5,2,0)),"",VLOOKUP(A1,Sheet3!
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.
.

 

[Ken Wright]

Laziness > Clarity > Brevity

 

[Biff]

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

 

[Ragdyer]

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>

 

[Ken Wright]

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.......................

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>

 

[Aladin Akyurek]

If MS ever implements the additional optional argement for lookup
functions I argued for since eons...

=VLOOKUP(LookupValue,TableX,ColIdx,0,VLOOKUP(LookupValue,TableY,ColIdx,0,ReturnValue)

where ReturnValue is optional, which if omitted defaults to #N/A.

Looks nice, no?

 

[Ken Wright]

Works for me

 

[Ragdyer]

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>

 

[Ken Wright]

Never say Never, you might be surprised ;-)

 

[Ragdyer]

Excuse me sir, BUT ... exactly where did I say "Never" ?<g>