CODEWITHSUNDEEP
excelindexingmatch
Avinash Chugani
Avinash Chugani

Reputation: 13

Matching a cell to the closest highest value in another range

I have a number on the "buildup" tab of an Excel workbook and a list of random numbers on the "OH" tab of the same workbook. I am trying to match the number on the buildup tab to the closest highest value on the OH tab. I found this formula online and tweaked it to my cell references and sheet names, however it only seems to match the closest number and not highest closest number: 

=INDEX(OH!$B$2:$B$250,MATCH(MIN(ABS(buildup!AC8-OH!$B$2:$B$250)),ABS(buildup!AC8-OH!$B$2:$B$250),0))

OH is the sheet with the list of random numbers. buildup!AC8 is the cell reference of the number I'd like to match to the closest highest value on the OH tab.

Upvotes: 1

Views: 109

Answers (1)

chancea
chancea

Reputation: 5958

I would use an array formula with the SMALL or MIN function, entered with ctrl + shift + enter:

Using SMALL:

=SMALL(IF(OH!$B$2:$B$250>=buildup!AC8,OH!$B$2:$B$250),1)

Using this you can increment to the 2nd highest, 3rd highest, etc. Based on the second parameter. Also this will return #NUM! if no match is found.

Using MIN:

=MIN(IF(OH!$B$2:$B$250>=buildup!AC8,OH!$B$2:$B$250))

As @Byron pointed out, this will return 0 when there is no large number to match, so SMALL might be preferred.

Upvotes: 1

Related Questions