How to find the closest value in a range to a given lookup value?

katmarsar

New Member
Joined
Apr 17, 2012
Messages
12
Is there a function or combination of functions that finds the exact or closest value in a range of values when the range of values is compared to a given lookup value given the following constraints.

1. The range of values is unsorted.
2. The range of values may contain zero values.

Thanks
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Hi,

A1:A19 is the area where you are searching,
B1 is the value to be (approximately) found
C1 is helper (displays the minimal distance between what you are searrching for and what is present in the data)
D1 is the closest value that has been found

Please read my signature at the bottom.

Best,

J.Ty.
Excel Workbook
ABCD
11615116
219
326
48
526
617
726
Sheet1
 
Upvote 0
Single cell solution, although it is a little long.
With the Range being search in A1:A13
With the Value being looked up in B1


If you have Excel 2003 or earlier you will need to use
=INDEX(A1:A13,IF(ISERROR(MATCH(B1-MIN(ABS(B1-A1:A13)),A1:A13,0)),MATCH(B1+MIN(ABS(B1-A1:A13)),A1:A13,0),MATCH(B1-MIN(ABS(B1-A1:A13)),A1:A13,0)))
Confirm with CTRL+SHIFT+ENTER

If you have Excel 2007 or later you can use this slightly shorter version
=INDEX(A1:A13,IFERROR(MATCH(B1-MIN(ABS(B1-A1:A13)),A1:A13,0),MATCH(B1+MIN(ABS(B1-A1:A13)),A1:A13,0)))
Also confirmed with CTRL+SHIFT+ENTER
 
Last edited:
Upvote 0
Hi

Another option

Using the list values in A1:A7 and the lookup value in B1, try:

=INDEX(A1:A7,MATCH(MIN(ABS(A1:A7-B1)),ABS(A1:A7-B1),0))
This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER.
 
Upvote 0
Interesting. Looks like it might work. What would I need to modify if the range is horizontal instead of vertical?
 
Upvote 0
To change the reference to horizontal instead of vertical, just change A1:Ax to A1:Z1 (or whatever your range is)
 
Last edited:
Upvote 0
I must apologize to everyone who has graciously posted a reply to my query. I omitted one important piece. I'm actually trying to locate the relative location of the nearest match within the array, not the actual nearest value. Many thanks.
 
Upvote 0
I must apologize to everyone who has graciously posted a reply to my query. I omitted one important piece. I'm actually trying to locate the relative location of the nearest match within the array, not the actual nearest value. Many thanks.


Actually, that is easy since that is the internal piece of the INDEX formula already.
Using pgc01's formula since it is clearly the best.

with the lookup range in A1:Z1
and the lookup value in A2

=MATCH(MIN(ABS(A1:Z1-A2)),ABS(A1:Z1-A2),0))
Confirm with CTRL+SHIFT+ENTER
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,148
Members
448,552
Latest member
WORKINGWITHNOLEADER

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
Back
Top