Formula in Excel to lookup for approximate match based on average, using Index/Match

Question

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

Answer 1

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))