Lookup various possible configurations

[dluhut]

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

[NBVC]

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)....

[dluhut]

Hi NBVC,

Thank you for your reply to this thread.

Just to make sure that I understand your formula correctly, it seems that you're trying to substitute whatever forms that 'PAPER' might come up with to 'JANE SMITH', although I don't know what those number 9.999999E+307 is about.

Anyway, my main point is that, the ones that I have bordered filled, which is cell A13 to B14, is my desired answer, thus, through vlookup/lookup or whatever it is, whenever there's variations of 'PAPER', I could have 'JANE SMITH' as an answer, by a "table" of names of A2 to B8

[NBVC]

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.

[dluhut]

NBVC,

First of all, my deepest apology of not making myself clear. The bordered that I have filled, was basically the answered that I desire to obtain as 'JANE SMITH' with the thought of vlookup.

Attached is a new spreadsheet that I believe would be clearer.

I kindda know what your formula was doing, which is assigning to 'JANE SMITH' whenever the word 'PAPER' is found. Again, this mis-understanding was made because I wasn't clear in my explanation.

Basically, what I had in mind wasn't assigning but more on a vlookup/lookup of each cell and assign it to the correct person's name from a "table".

Last but not least, your help is deeply appreciated!

[NBVC]

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...

[NBVC]

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.

[NBVC]

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

Please Login or Register  to view this content.

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

[dluhut]

Thanks NBVC! Your help is greatly appreciated! By the way, I copied your INDEX formula on cell G16 and copied down, and it gave me #VALUE!. Any idea where did I went wrong?

[NBVC]

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....

[dluhut]

NBVC,

THANK YOU! It seems that this is going to work! Even if it doesn't, at least, it saves me tons of eye-balling through thousands of lines.

Quick question, will this code works if I do have a company's name that has a letter 'z' in it? Although I still do not know that choose function, but let me do my own homework.

Once again, thank you!

PS: Please confirm if your reputation has increased, since I've just added mine to yours.

[NBVC]

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