Miltiple IF Statement

[Msgjazz]

Default Re: using INDIRECT with VLOOKUP




Thank you all for your help. I've been out for a while. I have a different question. I'm trying to do a multiple IFS statement where it matches two cell in cell BF4

Sample File Link

So IF A2 (ENF) matches C1 (ENF) and B2 matches anything in G2-G7 (HELB, HOCS...) than I should get a 1, otherwise 0.

Thanks

 

[dreid1011]

How about this:

=IF(AND(A2=C1,ISNUMBER(MATCH(B2,G2:G7,0))),1,0)

?

 

[jtakw]

Hi,

Another way, in C2 copied down:

=--AND(A2=C$1,COUNTIF(G$2:G$7,B2))

***You need to re-enter your C1 "ENF", you have either a leading or trailing space, =LEN(C1) returns 4

 

[Msgjazz]

jtakw

That worked. Thanks. Yes, there was a leading space.

Question.... What is the purpose of the leading hyphens. Can you explain that to me?

 

[Msgjazz]

dreid1011

This worked. I forgot about the MATCH. Thanks

 

[dreid1011]

jtakw

That worked. Thanks. Yes, there was a leading space.

Question.... What is the purpose of the leading hyphens. Can you explain that to me?

AND() by itself returns TRUE or FALSE. The leading -- coerces the TRUE/FALSE to show as 1 or 0 instead. (your desired results)

dreid1011

This worked. I forgot about the MATCH. Thanks

And you're welcome. Glad it worked for you.

 

[jtakw]

jtakw

That worked. Thanks. Yes, there was a leading space.

Question.... What is the purpose of the leading hyphens. Can you explain that to me?

You're welcome, glad it works for you.

Looks like dreid already answered your question, the double negative ( -- ) converts TRUE to 1, FALSE to 0, you can also use +0, or *1, the N() Function also does the same.