Excel formula to check how much two entries match

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.