Looking for closest value without duplicates

gehawk

Board Regular
Joined
Apr 4, 2013
Messages
55
Hi guys,

I am looking for a way to match the closest value in a column without any duplicates.
I used the following method :

https://www.extendoffice.com/docume...est-nearest-value-greater-than-less-than.html

This method worked fine : however I am looking for one extra thing : the matches need to be unique (i.e 1 value can only be used once to match).

I am not sure what would be the best way maybe : once the first match is done the formula calculates the best match for the remaining ones.

Hope someone can help me

Thanks
Gehawk
 

Some videos you may like

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Gehawk,

What is required is the set of desired results, not the results of an (almost or non-working) formula. Is it possible that you post in column C those values manually and remove the formulas?
 

gehawk

Board Regular
Joined
Apr 4, 2013
Messages
55
Hi the expected results are in column G, it's the closest match to the value in column A but it has to be unique.
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Hi the expected results are in column G, it's the closest match to the value in column A but it has to be unique.

Not sure as these are formula calculations.

1. What does ABS(F2-F2) in =IF(ABS(E2-A2) < ABS(F2-F2),E2,F2)<abs(f2-f2),e2,f2) g2?
mean?
2. What does "With number 1 belongs 13" mean?</abs(f2-f2),e2,f2)>
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,122,207
Messages
5,594,843
Members
413,944
Latest member
3xc3ln00b

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top