Thanks:  0
Likes:  0

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

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

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

2. Re: How to find the closest value in a range to a given lookup value?

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

Best,

J.Ty.
Sheet1

 A B C D 1 16 15 1 16 2 19 3 26 4 8 5 26 6 17 7 26

 Cell Formula A1 =RANDBETWEEN(1;30) C1 {=MIN(ABS(A1:A19-B1))} D1 =IFERROR(VLOOKUP(B1+C1;A1:A19;1;FALSE);B1-C1) A2 =RANDBETWEEN(1;30) A3 =RANDBETWEEN(1;30) A4 =RANDBETWEEN(1;30) A5 =RANDBETWEEN(1;30) A6 =RANDBETWEEN(1;30) A7 =RANDBETWEEN(1;30)
Formula Array:
Produce enclosing
{ } by entering
formula with CTRL+SHIFT+ENTER!

Excel tables to the web >> Excel Jeanie HTML 4

3. Re: How to find the closest value in a range to a given lookup value?

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

4. Re: How to find the closest value in a range to a given lookup value?

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.

5. Re: How to find the closest value in a range to a given lookup value?

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

6. Re: How to find the closest value in a range to a given lookup value?

Originally Posted by katmarsar
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?

7. Re: How to find the closest value in a range to a given lookup value?

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

8. Re: How to find the closest value in a range to a given lookup value?

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.

9. Re: How to find the closest value in a range to a given lookup value?

Originally Posted by 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.

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

10. Re: How to find the closest value in a range to a given lookup value?

That's it. Thanks.

User Tag List

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•