Excel formula to check how much two entries match

Excel formula to check how much two entries match

February 20, 2020

Here's a way to use formulas in Excel to compare how much entries in two cells match one another  - - based on the percentage of characters which match up from the left side of each cell. 

 

1. In this example we have two different spellings for cities listed in column A and column B. 

 

2. In column C we use the LEN formula to get the number of characters used for the version of the city name in column A. 

 

 

3. In column D enter this formula:

 

=SUMPRODUCT(
--(LEFT(A2,
ROW(INDIRECT("A1:A" & C2)))
=LEFT(B2,
ROW(INDIRECT("A1:A" &C2)))))
 

This formula is unusual because it must be entered on separate lines.   The SUMPRODUCT formula is used to get a result based on the left side of data in column A and column B - taken in conjunction with the LEN result in column C.   The resulting number is how many of the characters from the left in column A are matched in column B. 

 

 

 

4. In column E, change the format to percentage and divide the formula results in column D by the LEN result. 

 

 

 

The percentage shows how much of the left side of the entry in column A matches up with the entry in column B. 

 

Thanks to UniMord for posting this formula here.  

Please reload

Contact Me With Your Litigation Support Questions:

seankevinoshea@hotmail.com

  • Twitter Long Shadow

© 2015 by Sean O'Shea . Proudly created with Wix.com