VLOOKUP & TWO DIFFERENT RANGES

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

SSJ - 31 Aug 2007 14:35 GMT

Hello All,

Can VLOOKUP lookup in two different different ranges residing in separate
worksheettabs? In other words, if a match is not found in the first range
then lookup the 2nd range.

Can someone help in constructing such formula?

Regards
SJ

Reply to this Message

Mike H - 31 Aug 2007 15:08 GMT

one way

=IF(ISNA(VLOOKUP(D1,Sheet1!A1:B11,2,FALSE)),VLOOKUP(D1,Sheet2!A1:B11,2,FALSE),VLOOKUP(D1,Sheet1!A1:B11,2,FALSE))

Mike

> Hello All,
>
[quoted text clipped - 6 lines]
> Regards
> SJ

Reply to this Message

SSJ - 31 Aug 2007 17:46 GMT

Mike,

It worked perfectly!

Thank you
SSJ

> one way
>
[quoted text clipped - 12 lines]
>> Regards
>> SJ

Reply to this Message

hitesh - 08 Sep 2007 13:12 GMT

hi
mike
for vlookup. if range is more than 2, what changes have to make in formula,
and also mike i want to know about sum formula like v lookup more than 2
range.

regards
anil u

> one way
>
[quoted text clipped - 12 lines]
> > Regards
> > SJ

Reply to this Message

Pranav Vaidya - 31 Aug 2007 15:10 GMT

HI SSJ,

To the best of my knowledge, vlookup() can refer to only one search range,
however you can club it with IF and construct a formula. Here you go

cell A1 is the value to be serched
Range B1 to D100 is the first range to search
Range F1 to H100 is the first range to search

then try,
=IF(ISERROR(VLOOKUP(A1,$B$1:$B$100,2,0)),vlookup((A1,$F$1:$H$100,2,0),VLOOKUP(A1,$B$1:$B$100,2,0))

Thsi formula will check for the first range search, if unsuccessful will
search the second range.

Hope this helps!!

Signature

Pranav Vaidya
VBA Developer
PN, MH-India
If you think my answer is useful, please rate this post as an ANSWER!!

> Hello All,
>
[quoted text clipped - 6 lines]
> Regards
> SJ

Reply to this Message

T. Valko - 01 Sep 2007 04:42 GMT

Another one:

=VLOOKUP(A1,IF(COUNTIF(B1:B5,A1),B1:C5,Sheet2!B1:C5),2,0)

Signature

Biff
Microsoft Excel MVP

> Hello All,
>
[quoted text clipped - 6 lines]
> Regards
> SJ

Reply to this Message