VLOOKUP & TWO DIFFERENT RANGES
 

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

VLOOKUP & TWO DIFFERENT RANGES
 

one way


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


Mike


"SSJ" wrote:

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

VLOOKUP & TWO DIFFERENT RANGES
 

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

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


"SSJ" wrote:

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

VLOOKUP & TWO DIFFERENT RANGES
 

Mike,

It worked perfectly!

Thank you
SSJ


"Mike H" wrote in message
...
one way


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


Mike


"SSJ" wrote:

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

VLOOKUP & TWO DIFFERENT RANGES
 

Another one:

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

--
Biff
Microsoft Excel MVP


"SSJ" wrote in message
...
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

vlookup more than one range
 

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


"Mike H" wrote:

one way


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


Mike


"SSJ" wrote:

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