Can I make this equation easier?

SensualCarrots

New Member
Joined
Mar 21, 2015
Messages
46
I am writing equations to check a cell for a range of values, which over time, will grow to be quite extensive. If the cell contains any of the specified values, then enter a particular value. I have the cell currently functioning like this:

=IF(OR(ISNUMBER(SEARCH("715",$G1)),ISNUMBER(SEARCH("717",$G1)),ISNUMBER(SEARCH("718",$G1)),ISNUMBER(SEARCH("94",$G1)))," -1"," ")

I would like to do something like this instead:

=IF(ISNUMBER(SEARCH(OR("715","717","718","94"),$G1))," -1"," ")

This would make things much easier in the future to add new values to my search. The formula does not give an error, but always returns a false value. Any thoughts? Thanks in advance!
 

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.
You were close, try this

=IF(OR(ISNUMBER(SEARCH({"715","717","718","94"},$G1)))," -1"," ")

but enter it with Ctrl + Shift + Enter, not just Enter.
 
Upvote 0
Here's a Non array entered version.

=IF(SUM(COUNTIF($G1,"*"&{"715","717","718","94"}&"*"))," -1"," ")
 
Upvote 0
So I used Juan's solution, since it was the one I understand the most. Strangely though, it works even without Ctrl+Shift+Enter. Just for my own sake, any ideas why? Also, Andrew and Jonmo, could you explain how your solutions work? I know that they do, but while trying to learn all I can about excel, I'd like to try and understand why. Thanks again!
 
Upvote 0
Sure

=IF(SUM(COUNTIF($G1,"*"&{"715","717","718","94"}&"*"))," -1"," ")

Countif is normally used to count how many cells in a range equal a specified value.
=countif(A1:A10,"hello")
How many cells in A1:A10 are equal to "hello"

The * can be used as a wildcard.
=countif(A1:A10,"*hello*")
Now it will find hello anywhere within a cell like "I'd say hello, but I don't want to"

And it doesn't have to be used on a range of cells, it can be used on a single cell as well.
Very common method to test if a cell 'contains' a substring.
You can't do =A1="*hello*"
That would not use the * as a wildcard, it would consider it literally.
So you can use
=countif(A1,"*hello*")
If that returns 1, it found "hello" in A1, 0 means it didn't.

Now the context of
=SUM(countif(A1,{"*hello*","*goodbye*"}))
This basically creates an array of 2 results of the countif. 1 for hello and 1 for goodbye
The SUM then sums the results of them.

Then you Test if that result is 0 or Greater than 0
If it's 0, IF will consider it FALSE, any number other than 0 will be considered TRUE.
=IF(countif(....),do this, do that)

Hope that helps.
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,140
Members
448,551
Latest member
Sienna de Souza

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