CODEWITHSUNDEEP
excelsearchexcel-formula
Dangerous Game
Dangerous Game

Reputation: 103

Using Search Function across multiple columns

I'm trying to figure out how to use the search function across multiple cells (that go horizontally). I can't seem to figure out what's wrong with my formula. I'm trying to search for the word in E16 ("the") across the cells D4-AQ4. The word is in actually D4 so it should turn up as found, but it doesn't. It says not found, however if I just change the formula to just look in D4 it says found. The problem is I need this formula to work so I can test for more words across all those cells to see if they are there or not. Can anyone tell me what's going on?

=IF(ISERROR(SEARCH(E16,$D$4:$AQ$4)>0),"NOT FOUND","FOUND")

Upvotes: 2

Views: 14571

Answers (1)

JNevill
JNevill

Reputation: 50019

You can't have a range in =SEARCH(), it will just return a #VALUE which, in your formula will give you a "Not Found". When in doubt, take out your IF and see what the conditional statement returns.

You can, however, concatenate your cell values together to make a big fat search term. The =CONCATENATE() formula will work, you can just use &:

=IF(ISERROR(SEARCH(E16,CONCATENATE(D4, E4, F4, ... AQ4))>0),"NOT FOUND","FOUND")

or

=IF(ISERROR(SEARCH(E16,D4&E4&F4& ... &AQ4)>0),"NOT FOUND","FOUND")

Either way that one will work. That concatenation is pretty tedious to write, but it's your best bet outside of a quick VBA UDF.

Upvotes: 3

Related Questions