Search function across multiple cells

wbstadeli

Board Regular
Joined
Mar 11, 2016
Messages
151
Office Version
  1. 365
Platform
  1. Windows
Hi All,

I have the following search formula in Cell F1, which works great and enters "A1"value if true and "blank" if false.
Code:
=IF(ISNUMBER(SEARCH(G1,B1)),A1,"")
However when i change the "within text" to look at more cells in the same row, the formula doesn't work. See example below:
Code:
=IF(ISNUMBER(SEARCH(G1,[COLOR=#ff0000]B1:E1[/COLOR])),A1,"")
Couple notes: Im using cell G1 as a linked cell from a combo box, so im able to type in a keyword for filtering. I want to be able to type multiple words for a more refined filter, and if all the words match, then returns value of cell "A1" (which is just a row number).

Example:
ABCDEFG
11Thedoglikesmeinput formula hereLinked cell from combobox for keyword/s
2

<tbody>
</tbody>

This is what i need the formula to be in words:
Cell F1=Search cells B1 thru E1, using the keyword/s in Cell G1, and if they contain the keyword or words, then return A1, otherwise a blank.

Also i would like it to not matter what order i type it in, or Uppercase/lowercase,or even a space, just as long as it contains the letters/numbers in the keyword cell, for example the following searches need to return A1 into F1:
the
thedog
the dog
dog the
etc....
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
If you have a single keyword, you can use this:

=IF(COUNTIF(B1:E1,"*"&G1&"*"),A1,"")

If you have multiple keywords, it gets complicated real fast. First, how are they entered in the cell? Space delimited, comma delimited? Is there a maximum number of words you can enter? Also, do you want a positive result (A1) if you find any of the keywords, or do you require all the keywords to be found to get a positive result?
 
Upvote 0
Thank you Eric for your reply!

-So they would be entered in the cell with space delimited, however if i could have it so the space/no space would work that would be best. (if that makes it too complicated lets not worry about making it work without spaces...)
-max number of words to enter? no
-positve result if any keywords, or all keywords? All keywords, that way i can filter down a bunch of rows with the more keywords i type...
 
Upvote 0
OK try this:

=IF(PRODUCT(IFERROR(COUNTIF(B1:E1,"*"&TRIM(MID(SUBSTITUTE(G1," ",REPT(" ",250)),(ROW(INDIRECT("1:10"))-1)*250+1,250))&"*"),1)),A1,"")

When you put this in the formula bar, confirm with Control+Shift+Enter.

This should work for up to 10 key words, change the 10 to something higher if you want, but be aware that at some point, there will be problems. You can try to raise all the 250s in the formula if you want to try to fix that.

I can't think of a way to avoid using spaces to separate your key words. Let me know how this works.
 
Upvote 0
Yeah, no worries about doing it without spaces, that wouldn't be necessary. This almost works!!
A couple things i noticed:
-once one keyword matches it will return A1 regardless of what i type next for a keyword.
-does this not allow me to use numbers as keywords? in the example we are working with i know doesn't have any numbers in it, however my actual data that this formula will look at will have cells with numbers that i will use to filter.
-i tested current formula with numbers and it returns a blank as soon as i start to type a number.
 
Upvote 0
As far as your first point, I'm not seeing it. With the sample from your first post, I put "dog house" in G1, and the formula returned an empty cell. What values are you using?

And your second point, COUNTIF treats text and numbers differently, and the way I split up your keywords, internally they all are text, even the numbers. I'll think about it, but I don't see any easy way to get around that. Would you be open to using a UDF?
 
Upvote 0
Issue one is working now :). I apologize, i didn't do the Control+Shift+Enter.

For second issue, what is a UDF?
 
Upvote 0
OK, a few options.

COUNTIF treats text and numbers different, but you can force numbers to be text. If you select your B:E columns and format them as text, the existing formula should work. The problem is that it won't change existing values in those cells from numbers to text. You'll have to reload them.

Next, I came up with a version that doesn't use COUNTIF:

=IF(PRODUCT(MMULT(ISNUMBER(SEARCH(TRIM(MID(SUBSTITUTE(G1," ",REPT(" ",250)),(ROW(INDIRECT("1:"&LEN(G1)-LEN(SUBSTITUTE(G1," ",""))+1))-1)*250+1,250)),B1:E1))+0,TRANSPOSE(COLUMN(B1:E1)))),A1,"")

also with Control+Shift+Enter. As you can see, it's much more complicated, and you might have issues maintaining it. But it seems to work OK.

Finally, a UDF stands for User-Defined Function. Using VBA, which is the scripting language of Excel, it's possible to write your own functions. In this case, on the worksheet it might look like:

=CheckKeywords(A1,B1:E1,G1)

This will make your workbook macro-enabled though. Some people don't like to do this because of the risk of macro viruses, and some companies don't allow it. If you want to try it, open a COPY of your workbook. Press Alt-F11 to open the VBA editor. From the menu, select Insert > Module. Paste the following code into the window that opens:

Code:
Public Function CheckKeywords(ByVal MyRes As Range, ByVal MyCells As Range, ByVal KeyRange As Range)
Dim MyKeys As Variant, str1 As String, x As Variant, i As Long

    Application.Volatile
    CheckKeywords = ""
    MyKeys = Split(LCase(KeyRange.Value))
    str1 = ""
    For Each x In MyCells
        str1 = str1 & LCase(x.Value) & "|"
    Next x
    For i = 0 To UBound(MyKeys)
        If InStr(str1, MyKeys(i)) = 0 Then Exit Function
    Next i
    
    CheckKeywords = MyRes.Value
End Function
Press Alt-Q to close the editor. Enter the function as shown above.

Aside from the macro-enabled issues, there are probably more people who can help you with the UDF than with the formula. In any case, let me know what you end up doing.
 
Upvote 0

Forum statistics

Threads
1,213,543
Messages
6,114,236
Members
448,555
Latest member
RobertJones1986

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