Formula not working, work around?

[Camel123]

Hi,

=IF(A1={77;88;99}&" *";"OK";"")

A1 cell value= 77 Hamburger

Why does the formula return FALSE and not TRUE?

Appreciate your input.

 

[AlanY]

try

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH({77;88;99},A1))))>0;"OK";"")

 

[Camel123]

Worked perfect! Thank you mate. Just to understand, the two hyphen "--" just before (ISNUMBER...), not familiar with this, what purpose does it fulfill? if I remove them the formula stops working.

 

[AlanY]

Worked perfect! Thank you mate. Just to understand, the two hyphen "--" just before (ISNUMBER...), not familiar with this, what purpose does it fulfill? if I remove them the formula stops working.

you're welcome
the -- convert the true/false in an array to 1/0 etc so that the sumproduct() can works on it

 

[Camel123]

Now it is more clear to me regarding that. Though, I am a bit confused about using the =sumproduct in this context. What it usually does it multiplying one array with another and returns the product of those values, as far as I understand. In this context I can not really see what it does. Can you explain?

 

[AlanY]

i'm sure there is other way round.

anyway, the search() function try to find if any match of 77, 88 & 99 within Cell A1
and the isnumber() will convert them into ture or false,
the -- convert them into 1 and 0.

the sumproduct() add them together if one or more match (1 or above)

 

[steve the fish]

Based on your first formula you want the cell to start with 77, 88, or 99 and then a space which the provided formula doesnt strictly do. You could try:

=IF(OR(--(ISNUMBER(SEARCH({77;88;99}&" ",LEFT(A1;3)))));"OK";"")

 

[Camel123]

Thanks!

I modified the formula and added "AND" since I want a second criteria:

=IF(SUMPRODUCT(--(AND(ISNUMBER(SEARCH({77;88;99},A1))))>0;"OK";"")

When 77 does not exist in A1 the formula evaluation display "FALSE;FALSE...." which is correct, when clicking next step, all "FALSE" turns to "TRUE", why?

 

[AlanY]

what's the 2nd criteria for the AND() function?

 

[Camel123]

Second criteria is=
B1<=0

It returns FALSE on whether A1 contains 77 or not, and since B1= -100 the second criteria is TRUE, the whole AND turns "TRUE" because the "FALSE" later turns to "TRUE".