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:


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:


  • Twitter Long Shadow

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