Getting False Statement. Multiple if

Figero21

New Member
Joined
Jan 10, 2018
Messages
11
If am attempting to create multiple if statements. I need excel to search for a specific word in one cell and change the criteria in other cell for greater than. If I do the beginning of the formula I have no problem. However when I change the search to another word it no longer works.

=IF(COUNT(SEARCH("11M",Sheet1!I2)),IF(AND(Sheet1!S2>Sheet3!I21),"Pass","Fail"))=IF(COUNT(SEARCH("5M",Sheet1!I2)),IF(AND(Sheet1!S2>Sheet3!I19),"Pass","Fail"))

This formula above I get ends with a False statement which is not working. However if I use the Formula below it will work. I need to expand upon this formula to work like I have above. I have a total of 9 different words that I need it to search.

=IF(COUNT(SEARCH("11M",Sheet1!I2)),IF(AND(Sheet1!S2>Sheet3!I21),"Pass","Fail"))

Any help would be much appreciative. Thanks
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.
that equal sign should be an comma IF
 
Last edited:
Upvote 0
The top formula is asking if two values are the same and will return only True or False.


[ IF(COUNT(SEARCH("11M",Sheet1!I2)),IF(AND(Sheet1!S2>Sheet3!I21),"Pass","Fail")) ] = [ IF(COUNT(SEARCH("5M",Sheet1!I2)),IF(AND( Sheet1!S2>Sheet3!I19),"Pass","Fail")) ]

The left hand side of that, IF(COUNT(SEARCH("11M",Sheet1!I2)),IF(AND(Sheet1!S2>Sheet3!I21),"Pass","Fail")), will return either "Pass", "Fail", or False.
If I2 has the substring "11M", then it will return "Pass" or "Fail"; otherwise False.
Similarly, if I2 has the substring "5M", the right side will return "Pass" or "Fail"; otherwise False.

If I2 contains both of those substrings, then the whole might evaluate to True.
IF I2 contains only one of one substrings, then the whole will evaluate to False.
If I2 contains neither of those substrings, then the whole will evaluate to True.

If you want the right side of the top formula to be the IF False argument of the IF, move a parenthesis.
 
Upvote 0
If I change the 2nd = to a comma then I get a #Value !

What I am looking to achieve is changing the word in cell I2 from 3m to 5M or 7M or 11M or 23M or 36M or 52M or 102M then it will calculate another figure in another cell. I.e. if I select 5M S2 might have an response with 600. I need to then verify that the end value in S2 is greater than 500 to pass. If it is a 3M then it needs to be greater than 300, 7M greater than 700, 11M greater than 1100 so on and so forth.

SizeFlRatePass / Fail
5M201800Pass

<tbody>
</tbody>
 
Upvote 0
try
=IF(COUNT(SEARCH("11M",Sheet1!I2)),IF(AND(Sheet1!S2>Sheet3!I21),"Pass","Fail"), IF(COUNT(SEARCH("5M",Sheet1!I2)),IF(AND( Sheet1!S2>Sheet3!I19),"Pass","Fail")))
 
Upvote 0
Why do you need AND?

The following are the same thing...

IF(AND(Sheet1!S2>Sheet3!I21)

==

IF(Sheet1!S2>Sheet3!I21
<strike>
</strike>
 
Upvote 0
=IF(Sheet1!S2 < IF(ISNUMBER(SEARCH("11M",Sheet1!I2)), Sheet3!I21, IF(ISNUMBER(SEARCH("5M",Sheet1!I2)), -Sheet3!I19))), "Pass", "Fail')
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top