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.Â Â