Remember Me?

#1
August 31st 07, 02:35 PM posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Oct 2006 Posts: 58
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

#2
August 31st 07, 03:08 PM posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Apr 2007 Posts: 11,501
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

#3
August 31st 07, 03:10 PM posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Jul 2007 Posts: 180
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

"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

#4
August 31st 07, 05:46 PM posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Oct 2006 Posts: 58
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

#5
September 1st 07, 04:42 AM posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
 external usenet poster First recorded activity by ExcelBanter: Nov 2006 Posts: 15,768
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

#6
September 8th 07, 01:12 PM posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
 external usenet poster First recorded activity by ExcelBanter: Aug 2006 Posts: 70
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

 Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On

 Similar Threads Thread Thread Starter Forum Replies Last Post SSJ New Users to Excel 5 September 8th 07 01:12 PM confused Excel Discussion (Misc queries) 2 August 10th 06 03:25 AM JerseyJR Excel Worksheet Functions 2 September 6th 05 06:37 PM steve alcock Links and Linking in Excel 3 May 3rd 05 02:57 PM chathag Excel Worksheet Functions 3 November 22nd 04 05:07 PM

All times are GMT +1. The time now is 08:05 AM.