Excel Magic Trick 323: Partial Text VLOOKUP (Fuzzy Match)

[zaance26]

Thank you Mike...

[zaance26]

If the first name and Lst name has spelling errors or space errors will it be possible to apply vlookup

[ExcelIsFun]

@zaance26 , Fuzzy Lookup will never work perfectly and will not always work with misspelled words. It may work ocassionally and other times it will not work. There is just no wway that will work 100% of the time with Fuzzy Math.

[fusionista]

Excellent vid and, subsequently, I found I can use the 1st wildcard method ("*"&A1&"*") to VLOOKUP A NUMBER (exact match) within a string of numbers when the 1st column in the table array is a concatenated list of numbers seperated by commas. I.E. I can vlookup 54321 from a concatenated cell that contains 12345, 23451, 54321. etc

[ExcelIsFun]

Cool! I am glad the video had some use.

[RevealUsYourLove]

Does it work for numbers? I used my formula on my own spreadsheet, but didn't work.

Can you check your private message. I sent you a link that i post on Mrexcel.

[Gbell3587]

how would you display more than 2 results - you have shown the first result and also the last result but what if there were 12 enteries that contained the text 'don' or 'cable and wireless'? I have a sheet where i want the user to be able to search 'jacket' and it should display all the different types of jacket we have. Can anyone help, i'll provide .xls file for example if you want. Thanks alot.

[ExcelIsFun]

Tru this video:

Mr Excel & excelisfun Trick 54: Approximate Lookup To Return Multiple Items

[Gbell3587]

@ExcelIsFun brilliant, thanks..........great explanation to each part of the formula too!

[ExcelIsFun]

I am glad it helped!

[emailuznow]

Hi,

What if i needed to extract both the matches found i.e duplicates, what formula can i use?

[ExcelIsFun]

Try this video:

Mr Excel & excelisfun Trick 54: Approximate Lookup To Return Multiple Items

[lamido7]

OMG you just saved my life. Thank you!!

[ExcelIsFun]

I am glad that the video helped!!

[MayhewFamily]

Thank you. I have reached out to Mrexcel. Sorry you are so busy. Best of luck.

-Trish

[MayhewFamily]

Is there a way to use lookup and match to find a value which corresponds to the last four digits of a specific date (year)when the lookup value is only listed once in a column but the rows are which correspond to the lookup value are listed in another column on several rows? Col A - Tenant, COl B date, Col C rent. But within B&C row 1-20. Row quantity varies based on tenant lease terms.

[ExcelIsFun]

I am currently overwhelmed at work (80 + hours per week)...

If you have specific Excel questions try this other Excel site:

mrexcel..com/forum

[investmentfraudtimes]

I'm trying to compare 2 columns similar to the exercise above. I need to know which last names match each other. I tried the Vlookup and Lookup and got errors.

The formula that I'm using is: =LOOKUP(2^15,SEARCH(D2,$A:$A),­$B:$B)

and the Returned Values display #NUM!

Am I using the proper formula and why is attempting to return a number value.

[ExcelIsFun]

If you are matching 2 columns, try:

Excel Magic Trick 528: Check Two Lists For Discrepancies MATCH and VLOOKUP functions

[investmentfraudtimes]

@ExcelIsFun

Thank You for the assistance

[ExcelIsFun]

You are welcome!

[planiolro]

If we have duplicates what the solution then?

[ExcelIsFun]

Dear planiolro,

The solution is already doing an aproximate match. I don't know how to do an aproximate aproximate match.

--excelisfun

[ExcelIsFun]

Dear planiolro,

If you do a search at the Mr Excel Message Board for "Fuzzy Match", there is some amazing VBA code that will help to reduce the duplicate problem.

--excelisfun