Finding Closest Match

A

andyiain

Hi,

I know there are methods to find the closest match to a number that is
either greater than or smaller than a reference number but how do you
find the closest number regardless of whether its larger or smaller.

Any help would be very welcome.

Regards
Andy.
 
H

Herbert Seidenberg

Assuming there are no duplicates in your list,
enter as an array formula
=SUMPRODUCT(((ABS(list-target))=MIN(ABS(list-target)))*list)
otherwise use this array formula for the first closest match
=INDEX(list,MATCH(MIN(ABS(list-target)),(ABS(list-target)),0))
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Understanding SUMPRODUCT 11
Index & Match Question 2
Vlookup Approximate Match Question 4
Double Match (one with less than or equal to) 0
VLookup - Not Exact Match 2
how do i look up the closest number valuein a lookup? 3
Closest value lookup? 11
index match function help me 3

Top