Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match and Vlookup issue
Hi, my workbook has 3 worksheets. On the first 2 worksheets are lists
of study numbers with various outstanding work (there will never be matching study numbers on these 2 sheets, it's one or the other). The first sheet is titled 'Current', the 2nd sheet is titled 'Waiting'. The 3rd sheet (called 'ATFs') is an additional list of study numbers that get's brought in from a separate report. I'm trying to use the Match or Vlookup formula to find study numbers on the ATFs sheet that match study numbers on one of the other 2 sheets. The formula that I have tried is: =IF(OR(MATCH($A3,'Current'!$A$2:$A$100,0)0,MATCH( $A3,'Waiting'!$A$2:$A$100,0)0),"Match","") The thing is with this, if I try the formual with the Match only looking at one sheet at a time, it seemily works - if there is a match, then I get the row number where the match occurs, but if there's not a match, I still get #NA. I've tried a variation of this with the Vlookup formula: =IF(ISNA(OR(VLOOKUP($A25,'Current'!$A$2:$A$100,1,F ALSE),VLOOKUP($A25,'Waiting'!$A$2:$A$100,1,FALSE)) ),"No","Match") This returns the word "Match" if there is indeed a match on the 'Current' sheet, but otherwise returns 'No', even if there is a match on the Waiting worksheet. I'm confused. Any help/advice would be greatly appreciated. Thanks. Frank |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match and Vlookup issue
On Jun 20, 1:10*pm, Phrank wrote:
=IF(OR(MATCH($A3,'Current'!$A$2:$A$100,0)0, MATCH($A3,'Waiting'!$A$2:$A$100,0)0),"Match","") That does not work because if either MATCH expression fails and returns the #N/A error, OR() will return an error. Try: =IF(ISNUMBER(MATCH($A3,'Current'!$A$2:$A$100,0))," Match", IF(ISNUMBER(MATCH($A3,'Waiting'!$A$2:$A$100,0)),"M atch","")) This is also more efficient because IF() will evaluate the second MATCH only if the first ones fails. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Match and Vlookup issue
Bingo! My mind got stuck in having to use the OR statement. It works
perfectly. Thank you! Frank On Mon, 20 Jun 2011 15:05:18 -0700 (PDT), joeu2004 wrote: On Jun 20, 1:10*pm, Phrank wrote: =IF(OR(MATCH($A3,'Current'!$A$2:$A$100,0)0, MATCH($A3,'Waiting'!$A$2:$A$100,0)0),"Match","") That does not work because if either MATCH expression fails and returns the #N/A error, OR() will return an error. Try: =IF(ISNUMBER(MATCH($A3,'Current'!$A$2:$A$100,0)), "Match", IF(ISNUMBER(MATCH($A3,'Waiting'!$A$2:$A$100,0))," Match","")) This is also more efficient because IF() will evaluate the second MATCH only if the first ones fails. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vlookup with Match - no results issue | Excel Worksheet Functions | |||
MATCH & INDEX ISSUE? | Excel Discussion (Misc queries) | |||
VLOOKUP/Index&Match data format issue | Excel Worksheet Functions | |||
Match Exact Issue | Excel Worksheet Functions |