VLOOKUP VS INDEX & MATCH | DATA Matching

[Joneye]

Hello all,

Using a mac so cannot post my data sadly. (Excel for Mac latest version).

Brief
* One workbook, data extract needs to be checked against another list. One source of data is from a CRM another from excel. All data has been cleaned and sat in two tabs.

Objective
* Check if items in list A match or are close to List B.

Example
* List A company name "Jon 123" (8000 indiviudal references)
* List B company name "Jon V1" (630 indiviudal references)

How on earch can i check list B against list A bringing in the cloest option for each line of A.

Help is appreciated.

 

[Aladin Akyurek]

Your remark "the closest option" makes it necesssary that you create here two small fake lists to illustrate this point.

 

[Joneye]

Your remark "the closest option" makes it necesssary that you create here two small fake lists to illustrate this point.

Im searching for a loook up funciton to match or get close to a company name, one database has JON XXX another due to input errors has JON YYY, simply looking for a fomula to return what is close so data can be sensor checked further.

 

[Aladin Akyurek]

Im searching for a loook up funciton to match or get close to a company name, one database has JON XXX another due to input errors has JON YYY, simply looking for a fomula to return what is close so data can be sensor checked further.

I'm not going to create an example input and an associated output. The following will be as sketchy as your way with fake ranges...


=LOOKUP(9.99999999999999E+307,SEARCH("JON",$A$2:$A$100),$B$2:$B$100)