Match and Vlookup issue

[Phrank]

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,FALSE),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

 

[joeu2004]

=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.

 

[Phrank]

Bingo! My mind got stuck in having to use the OR statement. It works
perfectly. Thank you!

Frank