Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
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
Posted to microsoft.public.excel.newusers,microsoft.public.excel.worksheet.functions
|
|||
|
|||
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
Posted to microsoft.public.excel.worksheet.functions,microsoft.public.excel.newusers
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP, changing ranges | Excel Discussion (Misc queries) | |||
vlookup on non-contiguous ranges | Excel Worksheet Functions | |||
multiple ranges on Vlookup | Excel Discussion (Misc queries) | |||
vlookup ranges | Links and Linking in Excel | |||
How do I do a VLOOKUP from 2 ranges and add the results together? | Excel Worksheet Functions |