A Microsoft Excel forum. ExcelBanter

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.

Go Back   Home » ExcelBanter forum » Excel Newsgroups » Excel Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read Web Partners

VLOOKUP & TWO DIFFERENT RANGES



 
 
Thread Tools Display Modes
  #1  
Old August 31st 07, 02:35 PM posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
SSJ
external usenet poster
 
Posts: 58
Default 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


Ads
  #2  
Old August 31st 07, 03:08 PM posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
Mike H
external usenet poster
 
Posts: 11,504
Default 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  
Old August 31st 07, 03:10 PM posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
Pranav Vaidya
external usenet poster
 
Posts: 180
Default 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
>
>
>

  #4  
Old August 31st 07, 05:46 PM posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
SSJ
external usenet poster
 
Posts: 58
Default 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  
Old 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
Default 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  
Old September 8th 07, 01:12 PM posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
hitesh
external usenet poster
 
Posts: 70
Default 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

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

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 04:00 AM.


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