Finding closest match

[Kritty]

Hi,

I'm having a bit of trouble writing a formula to help find the nearest match.

My search up value is 185. I would like to write a formula that will read the list of numbers below, and provide the nearest approximate match.

I have tried using both VLOOKUP and INDEX functions but I cannot seem to get it to work.

For some reason if I try either of those functions, it will always return the lowest value from the numbers below (-137.55) regardless of my "search up" value.

It's probably something basic I am missing but any help would be appreciated.

Thanks

300.01
268.91
238.56
208.94
180.03
151.81
124.24
97.33
71.04
45.35
0
-4.27
-28.13
-51.29
-73.79
095.64
-116.89
-137.55

 

[arthurbr]

As you can see at https://support.office.com/en-gb/article/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a the number list must be sorted in ascending or descending order depending if you are looking for the largest value that is less than or equal to lookup_valueor the smallest value that is greater than or equal tolookup_value .

 

[JustynaMK]

Hi Kritty,

Alternatively, if you cannot sort the data for some reason, try using a combination of INDEX and SUMPRODUCT array formula (ctrl+shift+enter):

=INDEX(A:A,SUMPRODUCT(--(ABS($A$1:$A$18-$C$5)=SMALL(ABS($A$1:$A$18-$C$5),1)),ROW($A$1:$A$18)))

I am assuming that your data is in cells A1:A18 and your lookup value is in cell C5 (e.g. 185).