Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old August 31st 07, 02:35 PM posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
SSJ SSJ is offline
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
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



  #2   Report Post  
Old 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
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   Report Post  
Old 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
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   Report Post  
Old August 31st 07, 05:46 PM posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
SSJ SSJ is offline
external usenet poster
 
First recorded activity by ExcelBanter: Oct 2006
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   Report Post  
Old 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
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   Report Post  
Old 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
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





Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


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:48 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017