CODEWITHSUNDEEP
excel-formulavlookup
Jason
Jason

Reputation: 47

ISNA: Return "YES", "NO" in nested formula

I'm trying to get it to return YES/NO instead of the the lookup value/#N/A by using ISNA, but I'm not sure where to place it.

Here is my formula:

=IF(AND([@Center]="London",[@Reportable]="YES"),VLOOKUP([@ID],Client!C:C,1,FALSE),"IGNORE")

Upvotes: 0

Views: 232

Answers (2)

Hallvardr
Hallvardr

Reputation: 1

try the following:

=IF(AND([@Center]="London",[@Reportable]="YES"),ISNA(VLOOKUP([@ID],Client!C:C,1,FALSE)),"IGNORE")

You have to wrap with ISNA the desired part of the formula to be affected.

I believe this is what you are looking for.

Nevetheless, this will output TRUE/FALSE, not YES/NO.

Hope it helps.

Upvotes: 0

Darren Bartrup-Cook
Darren Bartrup-Cook

Reputation: 19837

Try using MATCH as you only want to know if it's in the list. This will return its location in the list or #N/A (VLOOKUP may return something that's not a number).

You can then check if it's numeric and return Yes / No as required: 

=IF(AND([@Center]="London",[@Reportable]="YES"),IF(ISNUMBER(MATCH([@ID],Client!C:C,0)),"Yes","No"),"IGNORE")

Upvotes: 1

Related Questions