Find a non-case-sensitive text string within a range of cells

Question

I've got a bit of a problem to solve in Excel, and I'm not quite sure how to go about doing it. I've done a few searches online, and haven't really found any formulas that seem to be useful. Here's the situation (simplified just a bit, for the purpose of this question):

I have data in columns A-E. I need to match data in the cells in A and B, with data in C-E, and return TRUE or FALSE to column F.

Return TRUE if:
- The string in A is found within any string in C-E.
OR
- The string in B is found within any string in C-E.

Otherwise, return FALSE.

The strings must be exact matches for whole or partial strings within the range, but the matching function must be case-insensitive. I've taken a screenshot of an example sheet for reference.

I'm fairly sure I'll need to use IF or on the outermost layer of the formula, probably followed by OR. Then, for the arguments to OR, I'm expecting there will be some use of IFERROR involved. But what I'm at a loss for is the function I could most efficiently use to handle the text string searches.

VLOOKUP is very limited in this regard, I think. It may be workable to do whole-string against whole-string comparisons, but I'm fairly certain it won't return accurate results for partial string matches. FIND and SEARCH appear limited to only single-target searches, and are also case-sensitive. I suppose I could use UPPER or LOWER to force case-insensitivity in the search, but I still need something that can do accurate partial matching and search a specified range of cells.

Is there any function, or combination of functions, that could work here?

Ideally, I want to do this with a straight Excel formula. I'm not at all familiar with VBScript or similar tools, nor do I have time to learn it for this project.

Answer 1

This is my dirty solution, I am not sure it is the best one.

You should try to add a temporary column, then you can hide it later. And try to convert all the data to lowercase or uppercase and then follow the logic you have already done.

Just like this:

A2: ABcdeF
B2: =lower(A2) --> abcdef
C2: aBc
D2: =lower(C2) --> abc
E2: =find(D2, B2) --> found
F2: =find('poi', B2) --> not found

VLOOKUP function has an optional parameter: range_lookup

A logical value (TRUE or FALSE only) that indicates whether you want VLOOKUP to find an exact or an approximate match to the lookup_value

http://spreadsheets.about.com/od/excelfunctions/ss/vlookup_2.htm

Answer 2

Try to combine those cells text, this is my trick:

A1: a
A2: b
A3: c
B1: ">" & A1 & "<" --> >a<
B2: = B1 & ">" & A2 & "<" --> >a<>b<
B3: = B2 & "<" & A3 & "<" --> >a<>b<>c<

... you can automate by using excel, just believe it takes less than 1 min :-)

so the final cell should combine all text from all cells

Then just do the ordinary search, but make sure you include ">" and "<" to your criteria, something like

=find(">" & "abc" & "<", $B$3)

and again it is my dirty trick!

Answer 3

Ideally, I want to do this with a straight Excel formula. I'm not at all familiar with VBScript or similar tools, nor do I have time to learn it for this project.

VBA's Range.Find method exists only for such tasks. It would be a shame to not use it. :)

Syntax

Range.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte, SearchFormat)

We only need 3 of the parameters for your task. Leave the rest out

• Range: The range where to search in
• What: The data to search for
• LookAt: Can be one xlWhole (complete match) or xlPart (partial match)
• MatchCase: True to make the search case sensitive. False for a case insensitive search

A short example would look like

 Set result = Columns(C:E).find(what:=[A1], LookAt:=xlPart, MatchCase:=false)

---

This code respects your additional conditions like

• Check data from column A and column B. If one of them matches, return true
• Return true even with partial matches
• Ignore uppercase or lowercase
• Return true or false in column F

Sub SearchMatches()    
For i = 2 To 5    
    On Error Resume Next
    Set checkColA = Columns("C:E").Find(Cells(i, 1), , , xlPart, , , False)
    Set checkColB = Columns("C:E").Find(Cells(i, 2), , , xlPart, , , False)
    On Error GoTo 0        
    If checkColA Is Nothing And checkColB Is Nothing Then
        Cells(i, 6) = False
    Else
        Cells(i, 6) = True
    End If        
Next i    
End Sub