# Find closest match - fuzzy?

#### bromine81

##### New Member
Hi, I'm new to this forum - I must say I'm impressed, albeit overwhelmed, with all the solutions to a wide variety of problems! Here's my problem:

I'm trying to use excel to compare a numerical value with an identifying list and have it return the value in the column next to the closest match. Vlookup would work, except it won't find the closest match in either direction.

I've looked into fuzzyvlookup, but it seems that it's only for matching text, not the closest number. I can't get the function to return anything other than N/A. It is very possible that I am doing something wrong with it - but maybe this function wasn't made to match numerical values?

To clarify:

I'm trying to identify a chemical based on its retention time on a chromatographic column:

retention time / compound
34.867 PCB 45
35.543 PCB 48
35.789 PCB 41
36.534 PCB 54

What I'd like to do is input a number, like 35.756, and the function report back "PCB 41" because it's the closest match. Some of the other features in the fuzzyvlookup function like "rank" and "percentdifference" would be useful too, but not essential. The problem with vlookup is that it would report back "PCB 48" because it will ingore values greater than the lookup value. I'm looking for a function that can do this properly. Some of the other features in the fuzzyvlookup function like "rank" and "percentdifference" would be useful too, but are not essential.

This seems like a simple problem to have, and yet I can't seem to find the solution.

Any help would be greatly appreciated!

### Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

#### pgc01

##### MrExcel MVP
Hi bromine81
Welcome to the board

Try in E4:

=INDEX(\$B\$2:\$B\$5,MATCH(MIN(ABS(\$A\$2:\$A\$5-E2)),ABS(\$A\$2:\$A\$5-E2),0))

This in an array formula, you have to confirm it with CTRL+SHIFT+ENTER

#### bromine81

##### New Member
wow... thank you!!! Your method worked perfectly!

I tried finding a solution to this problem 2 years ago and came up with a method using macros that was so convoluted it was unusable.

Now that I have this, it will save me weeks of time!

THANKS!

Replies
1
Views
186
Replies
2
Views
799
Replies
1
Views
521
Replies
3
Views
259
Replies
7
Views
6K

1,191,163
Messages
5,985,028
Members
439,934
Latest member
Verdiana

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back