Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
WorkerBee
 
Posts: n/a
Default 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   Report Post  
Ken Wright
 
Posts: n/a
Default

=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   Report Post  
Ken Wright
 
Posts: n/a
Default

=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   Report Post  
Biff
 
Posts: n/a
Default

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   Report Post  
Ragdyer
 
Posts: n/a
Default

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   Report Post  
Ken Wright
 
Posts: n/a
Default

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   Report Post  
Bob Phillips
 
Posts: n/a
Default

.... 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   Report Post  
Ken Wright
 
Posts: n/a
Default

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   Report Post  
Bob Phillips
 
Posts: n/a
Default

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   Report Post  
Ken Wright
 
Posts: n/a
Default

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   Report Post  
Bob Phillips
 
Posts: n/a
Default

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   Report Post  
Ken Wright
 
Posts: n/a
Default

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   Report Post  
Biff
 
Posts: n/a
Default

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   Report Post  
Ragdyer
 
Posts: n/a
Default

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   Report Post  
Ken Wright
 
Posts: n/a
Default

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   Report Post  
Aladin Akyurek
 
Posts: n/a
Default

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   Report Post  
Ken Wright
 
Posts: n/a
Default

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   Report Post  
Ragdyer
 
Posts: n/a
Default

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   Report Post  
Ken Wright
 
Posts: n/a
Default

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   Report Post  
Ragdyer
 
Posts: n/a
Default

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   Report Post  
Ken Wright
 
Posts: n/a
Default

LOL :-)

--
Regards
Ken....................... <snip


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copying multiple sheets from one book 2 another and undertake spec Pank Mehta Excel Discussion (Misc queries) 14 March 16th 05 05:41 PM
Sheets Skip Macro NICK Excel Discussion (Misc queries) 2 February 11th 05 08:16 AM
vlookup data hidden within worksheet Excel Worksheet Functions 0 January 26th 05 01:09 PM
VLOOKUP across all sheets in a workbook Pawel P. Excel Worksheet Functions 3 January 13th 05 01:20 AM
Multiple sheets selected twa14 Excel Discussion (Misc queries) 2 December 21st 04 12:15 PM


All times are GMT +1. The time now is 11:38 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"