Excel 2010 If cell matches any part of string in list

1
votes

I'm looking for a formula that can search a given list of strings and match a certain cell based on whether that cell contains text that appears in any part of a string from the list.

An example to show what I want to achieve:

Reference_List:
    Some Product A
    Another Example
    (XYZ) FinalTest
    ABC (Acronym Explanation)

List that I'm searching through:
    FooBar
    Another Example
    QuickBrownFox
    JumpedOverLazy Example
    Acronym Explanation (ABC)
    FinalTest (XYZ)

Ideal Matches Found:
    Another Example
    JumpedOverLazy Example (false positive but I would be okay with it)
    Acronym Explanation (ABC)
    FinalTest (XYZ)

So as you can see, it's testing the Search List against every sub-string in the Reference_List

Also, I don't mind if it contains false positives, but currently I'm running into too many false negatives with things like: (where List_REF is the name of cells e.g. A1:A20)

=SUMPRODUCT(--ISNUMBER(SEARCH(LIST_REF,A1)))>0

I've also looked at this example but I was unable to get it to work properly

Any help would be appreciated, Thank you

Edit:

Another Idea? Maybe if I could split each cell in the reference list into an array based on "spaces", then feed those arrays into the SUMPRODUCT function seen above that would search every cell by the sub array? Don't know how to do it but I'm gonna look into it

1
excelexcel-formulaexcel-2010
Microsoft's Fuzzy Lookup add-in might be helpful: microsoft.com/en-us/download/details.aspx?id=15011 – John Coleman
That looks like what I'm after, but any lighter versions without an installer or a way to do one as a formula? – sl133
Depends on what you want of course. Partial-matching is a tricky problem. If a good version which works nice with Excel already exists, why not use it? Anything which can be done with simple spreadsheet formulas probably wouldn't be flexible enough. – John Coleman
Do you define the words as separated by blank space?. If so then Acronym Explanation (ABC) it's also a false positive. Please clarify. Also are you willing to use a vba Function, or working columns? – EEM

1 Answers

0
votes

The below formula may be of help to you. With the Reference range column in A and the Search list in column G, apply this formula : =IFERROR(IF(EXACT(A2,G$2),G$2,IF(EXACT(A2,G$3),G$3,IF(EXACT(A2,G$4),G$4,IF(EXACT(A2,G$5),G$5,IF(EXACT(A2,G$6),G$6,IF(EXACT(A2,G$7),G$7,"")))))),"")