Formula not working, work around?

Camel123

Board Regular
Hi,

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

A1 cell value= 77 Hamburger

Why does the formula return FALSE and not TRUE?

Excel Facts

Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
try

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

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.

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

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?

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)

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";"")

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?

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

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".

Replies
3
Views
211
Replies
6
Views
299
Replies
7
Views
162
Replies
1
Views
259
Replies
2
Views
226

1,203,046
Messages
6,053,192
Members
444,644
Latest member
keepontruckinc4

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back