Excel 2012 - Vlookup with text

[Johnny Thunder]

Hello all


I need help with a funky lookup

In Column A & B

Cell A2 - Aegis IT LTD
Cell B2 - Aegis LTD INC.

I need a vlookup that will look in column A and see the "Aegis IT LTD" and then look in column B and because the "AEGIS LTD INC." is pretty darn close to correct return that as my answer.


The problem I am having is that for this example I could easily say look for "Aegis" and don't look at anything else. This type of Logic won't work for other Names in my criteria because in some cases the name could have been like "IT Aegis" and just for an example I used the whole "Aegis" thing.


The formula if possible should just look to Column A and see what is in there and find the closest match it can from Column B. Hope this is possible. Thanks for all the help!

Johnny

 

[Blue_Elliott]

Well, other than using the 'True' option when formulating the hlookup, this is pretty hard. If the data is consistent in that the first 5 letters are what you want to match then you can match on a left function

=left(text,5)

but if the text is inconsistent then you're probably out of luck. You might be able to make tables of similar values and then using if statements construct your hlookup so that if it doesn't find one value it goes to the next, but I'd have to know more about your data to know if this is viable.

 

[Johnny Thunder]

The function will be a Vlookup.

And the Left or Right function won't work. I was hoping there might be a way to use Wild Cards "*" to get the results I needed but I am not sure how to say look in cell A2 and use what you see in there and add "*" to the beginning and end of the contents of the cell and match to the cell in B2.

The data runs down A1 =Header Row. Cells A2:A400 matching to cells B2:B400.