Look for match on two worksheets

R

roy.okinawa

I have this formula that looks for a match on another worksheet:

=IF(ISNA(MATCH(F8,NSN6!A:A,0)),"N/A",INDEX(NSN6!D:D,MATCH(F8,NSN6!A:A,0)))

I need this scenario added to the formula: First look at NSN6 for match.
If found, input match. If no match found, look on NSN5 for match. If found,
input match. If not found on either worksheet, input N/A.
 
B

Biff

Hi!

One way:

=IF(COUNTIF(NSN6!A:A,F8)+COUNTIF(NSN5!A:A,F8)=0,"N/A",IF(COUNTIF(NSN6!A:A,F8),INDEX(NSN6!D:D,MATCH(F8,NSN6!A:A,0)),INDEX(NSN5!D:D,MATCH(F8,NSN5!A:A,0))))

Biff
 
R

roy.okinawa

That did it.

Thanks again.

Biff said:
Hi!

One way:

=IF(COUNTIF(NSN6!A:A,F8)+COUNTIF(NSN5!A:A,F8)=0,"N/A",IF(COUNTIF(NSN6!A:A,F8),INDEX(NSN6!D:D,MATCH(F8,NSN6!A:A,0)),INDEX(NSN5!D:D,MATCH(F8,NSN5!A:A,0))))

Biff
Click to expand...
 
Top