0

How can I use excel formulae such as VLOOKUP or INDEX MATCH to lookup a value from a range, based on the mean of the upper and lower values when exact match is not available. e.g.

Range:

A. B.
100 123
125 234
175 345
276 547
C Input D Lookup value
200 345
250 547

Since 200 does not have an exact match in column A, I need the value corresponding to 175 because 200 is less than the mean (175+276)/2 (=225); similarly 250 has a lookup value of 547 because 250>225.

I have tried:

= INDEX(B1:B4,MATCH(TRUE,A1:A4>C1,0)) --> always gives the upper bound

= VLOOKUP(C2,$A$1:$B$4,2,TRUE) --> always gives lower bound
Scott Craner
  • 148,073
  • 10
  • 49
  • 81
ThinkWiz
  • 1

1 Answers1

2

Use INDEX/AGGREGATE:

=INDEX(B:B,AGGREGATE(15,7,ROW($A$1:$A$4)/(ABS($A$1:$A$4-C1)=MIN(ABS($A$1:$A$4-C1))),1))

enter image description here

Scott Craner
  • 148,073
  • 10
  • 49
  • 81