Using Defined Names in a IF Statement

[tycasey17]

A1 = PVT

=IF(A1=_OFF,"Yes","No")

The Defined Name of "_OFF" list officer ranks and PVT would not be one of them so the result should be NO. However, this formula doesn't work it returns a #VALUE! error. I was trying to avoid using a VLOOKUP statement for this to keep my formula a little cleaner because this will be a series of nested IF statements.

 

[Aladin Akyurek]

What is the +0 at the end do.

Yes/No is not that useful as result. And harder to process. You need COUNTIF(S) to count the Yes out come.

MATCH delivers either a number or an #N/A value.

ISNUMBER(MATCH(...)) delivers either TRUE or FALSE.

+0 of

ISNUMBER(MATCH(...))+0

coerces a TRUE into 1 and a FALSE into a 0. (By the way, TRUE = 1 and FALSE = 0, in Excel.)

So, we get a 1 for a hit (instead of Yes) and a 0 for a miss (instead of No). To SUM 1's is easier/handier than applying COUNTIFS.

 

[Aladin Akyurek]

Faster, yes, but not quite as fun

What makes it more appropriate (besides being faster)?
I do like that MATCH can use wildcards which could be helpful here

MATCH is locating a value, while SUMPRODUCT is counting that value. The former is what is wanted.