Thread: Lookup various possible configurations

To all Excel Expert,

I've a problem that I've been trying to solve for months and since I've decided to have enough of manual check and eye-balling through thousands of lines or Excel, I've decided to put up this question to the world and seek for a solution.

My difficulty and desired answer to obtain is on attached file.

Basically, what I'm trying to do is that, assuming a company name called PAPER COMPANY INC, which is assigned to Jane Smith, has multiple name forms such as PAPER CO, PA P ER, PAP ER, P APER, etc, and through eye-balling or manual, we knew that all those various/similar 'paper' name were the same as PAPER COMPANY INC and should give a return value to 'Jane Smith'.

Thus, my questions is how do I go about using vlookup OR any other formulas in Excel to be able to return 'Jane Smith' as the answer, doesn't matter what are the variation of PAPER would come out.

Thank you

Try:

=LOOKUP(9.999999E+307,SEARCH(SUBSTITUTE($A$13," ",""),SUBSTITUTE(A2," ","")),$B$13)

copied down


If you have a table of items and names, then you can use something like:

=LOOKUP(9.999999E+307,SEARCH(SUBSTITUTE($A$13:$A$20," ",""),SUBSTITUTE(A2," ","")),$B$13:$B$20)

Note: You should not use merged cells though.....

These essentially remove all spaces and look for a match (sans spaces)....

That is what the 1st formula will do:

Enter:

=LOOKUP(9.999999E+307,SEARCH(SUBSTITUTE($A$13," ",""),SUBSTITUTE(A2," ","")),$B$13)

into B2 and copy down.

Yeah, not sure about that... there are too many variations.. and words contianing the P A P E R letters are in different possible outcomes.

You might want to do a search of fuzzy vlookup. There are some good vba codes out there, but I am not sure if they will get you your exact results. Maybe someone here with good VBA skills (or with a formula I haven't considered) might help...

This is the closest I got with a formula:

=INDEX($B$2:$B$10,MATCH("*"&SUBSTITUTE(A16," ","*")&"*",SUBSTITUTE($A$2:$A$10," ",""),0))

copied down.

Okay one even closer... get's all your current desired results:

=LOOKUP(REPT("z",255),CHOOSE({1,2,3},"",LOOKUP(9.999999E+307,SEARCH(SUBSTITUTE($A$2:$A$10," ",""),SUBSTITUTE(A16," ","")),$B$2:$B$10),INDEX($B$2:$B$10,MATCH("*"&SUBSTITUTE(A16," ","*")&"*",SUBSTITUTE($A$2:$A$10," ",""),0))))

but not sure it will work 100% in your real data.

Forgot to mention that you have to confirm the formula with CTRL+SHIFT+ENTER not just ENTER. It is an array formula.

See attached with the very latest formula....

Hi,

Yes, thanks. The reputation has increased.

The LOOKUP(REPT("z",255),CHOOSE({1,2,3} combination is a technique used for error trapping, and it basically chooses the output (one of the 3 conditions following the choose({1.2.3}).. it kind of works backwards... if the last conditions returns an error (i.e. match not found), then in looks at the 2nd condition, if that returns an error, then it looks at the first (i.e. it returns a blank).

If you are satisfied with the solution then please remember to mark your thread as Solved.

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save