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.

 

[AnalystJames]

http://www.mrexcel.com/forum/excel-questions/466854-check-if-value-present-list.html

Change

=IF(ISNUMBER(MATCH(B1,A1:A10,0)),1,0)

to

=IF(ISNUMBER(MATCH(A1,_OFF,0)),"Yes","No")

 

[Norie]

If the range _OFF consists of more than one value/cell then you can't compare it to a single value using =.

Can you explain how you will want to expand the formula?

PS To find out if a value is in a range you can use MATCH.

 

[ndsutherland]

Here's the cleanest i can think of

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

 

[AnalystJames]

Here's the cleanest i can think of

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

Very nice

 

[ndsutherland]

Thanks

 

[Aladin Akyurek]

Here's the cleanest i can think of

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

=IF(ISNUMBER(MATCH(A1,_OFF,0)),"Yes","No")

or just

=ISNUMBER(MATCH(A1,_OFF,0))+0


are faster and more appropriate.

 

[ndsutherland]

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

 

[tycasey17]

That worked great

 

[tycasey17]

=IF(ISNUMBER(MATCH(A1,_OFF,0)),"Yes","No")

or just

=ISNUMBER(MATCH(A1,_OFF,0))+0


are faster and more appropriate.

What is the +0 at the end do.