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

[katmarsar]

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

 

[J.Ty.]

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
	A	B	C	D
1	16	15	1	16
2	19
3	26
4	8
5	26
6	17
7	26
Sheet1

 

[BiocideJ]

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

 

[pgc01]

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.

 

[katmarsar]

Interesting. Looks like it might work. What would I need to modify if the range is horizontal instead of vertical?

 

[pgc01]

Interesting. Looks like it might work. What would I need to modify if the range is horizontal instead of vertical?

What did you try and what problems are you experiencing?

 

[BiocideJ]

To change the reference to horizontal instead of vertical, just change A1:Ax to A1:Z1 (or whatever your range is)

 

[katmarsar]

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.

 

[BiocideJ]

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

 

[katmarsar]

That's it. Thanks.