If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 VLOOKUP & TWO DIFFERENT RANGES
 Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

## VLOOKUP & TWO DIFFERENT RANGES

#1
August 31st 07, 02:35 PM posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
 SSJ external usenet poster 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
 Mike H external usenet poster 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
 Pranav Vaidya external usenet poster 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
 SSJ external usenet poster 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
 T. Valko external usenet poster 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
 hitesh external usenet poster 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
> >
> >
> >

 Thread Tools Display Modes Linear Mode

 Posting Rules You may not post new threads You may not post replies You may not post attachments You may not edit your posts vB code is On Smilies are On [IMG] code is On HTML code is Off
 Forum Jump User Control Panel Private Messages Subscriptions Who's Online Search Forums Forums Home Excel Newsgroups     Excel Discussion (Misc queries)     Setting up and Configuration of Excel     New Users to Excel     Excel Worksheet Functions     Links and Linking in Excel     Charts and Charting in Excel     Excel Programming About ExcelBanter     About this forum

 Similar Threads Thread Thread Starter Forum Replies Last Post VLOOKUP & TWO DIFFERENT RANGES SSJ New Users to Excel 5 September 8th 07 01:12 PM vlookup - choice of ranges confused Excel Discussion (Misc queries) 2 August 10th 06 03:25 AM VLOOKUP for Zip Code Ranges JerseyJR Excel Worksheet Functions 2 September 6th 05 06:37 PM vlookup ranges steve alcock Links and Linking in Excel 3 May 3rd 05 02:57 PM vlookup and named ranges chathag Excel Worksheet Functions 3 November 22nd 04 04:07 PM

All times are GMT +1. The time now is 01:00 AM.