Closest Match in a Range

[Bossi]

With a given cell, I'm trying to figure out how to identify the nearest match from within a range.

Using license tags & partial tags as an example in the image below... let's say I have a list of tags in Column A. Where I only have a partial tag, I use a lower-case "x" to denote an unknown digit.

What I want Column B to do is, if there are any unknown (x) digits, to scan all of Column A for the nearest match tag to the input for that row. So for the first partial tag (Row 5), cell B5 would look for the closest match to A5 in all of A:A.

Then Column C would give the % match. So C5 would go "it matches 7 out of 9 total digits", or 78%.

---
In Row 8 is an example where the input only has 7 digits, whereas everything else has 9. My preference is that this would return 0%, as there are no other 7-digit matches. But if the only way to get everything else to work is to treat this as if it's a 9-digit input with additional two unknown digits... I could probably work with that.

---
(Hopefully I didn't make any mistakes in the example. And I rather suspect there are lingering "But what about this scenario?" questions I'm not yet thinking of)

 

[Rijnsent]

There are a couple of ideas:
- for the lookup/match you can use the Fuzzy Lookup add-in: Download Fuzzy Lookup Add-In for Excel from Official Microsoft Download Center
- the match of two strings: How compare two strings and count the number of matched characters? -> this has two formulas that can do the job and count the number of matching characters. Another option is to go for "Levenshtein distance", which accounts a bit better for missing characters etc.

If you want all your business rules (like the NJ vs PA result), you probably have to write some VBA code to get that in.