I have a set of ID's and numbers - For example,

ABC - 123.45
ABC - 123.45
ABC - 123.44
CDE - 123.45
ABC - 123.33

Now, if I wanted to match things that were exactly the same, that's really easy - Create a unique ID with the letters and numbers, and do a match/vlookup on it to find ones that are the same to pair them with each other. My question is, how can I do a "fuzzy pairing" within a given tolerance?

One idea I have is this - for a .10 tolerance, I can have my unique ID be =A1&Round(B1,1), then do an exact match with that. Are there any other ways to do matching with some tolerance built in?


I assume, thought you don't say, you want to do this in a formula and you want to be able to use VLOOKUP with the result. You normally want to handle positive and negative differences within your tolerance, so you really need to find the absolute difference. I would add a column with a new formula based on the search value:

=A1&(ABS(FindNum-B1) < 0.10)


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.