Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

enter image description here

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.

share|improve this question

3 Answers 3

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

share|improve this answer
    
I've run VLOOKUP with TRUE and FALSE values before, and found the "approximate match" to be way off. I think they're using some really liberal meaning of the term "approximate". –  Iszi Apr 11 '12 at 4:12
    
This is my trick, please try and let me know. Please read my other answer in this thread –  Truong Ha Apr 11 '12 at 5:01

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!

share|improve this answer
    
Interesting idea. How do I "automate" this with Excel, though? I've got almost 900 rows to deal with. –  Iszi Apr 12 '12 at 13:50
    
Oops, just simply drag B3 down, excel will automatically copy the formula and update other cells. Or you can even point you mouse to the bottom right corner of B3 cell and double click it (you should see the mouse cursor turn to a cross +) –  Truong Ha Apr 13 '12 at 6:02

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

enter image description here

share|improve this answer
    
I know this question is 3 years old, but maybe others will find this useful –  nixda May 16 at 8:19

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.