Finding Closest Match

  • Thread starter Thread starter andyiain
  • Start date Start date
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.
 
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
How to determine the value? 8
Vlookup Approximate Match Question 4
Double Match (one with less than or equal to) 0
Closest value lookup? 11
How do I find the cell/data the closest/nearest to another data? 8
VLookup - Not Exact Match 2

Back
Top