Look for match on two worksheets

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

[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

"roy.okinawa" <[email protected]> wrote in message
news:[email protected]...
>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.
>

[roy.okinawa]

That did it.

Thanks again.

"Biff" wrote:

> 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
>
> "roy.okinawa" <[email protected]> wrote in message
> news:[email protected]...
> >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.
> >
>
>
>