How to nest INDEX and Match into IF statements?

Hi Matthew,

I'm not a real formula guy, but I managed to come up with a solution. The efficiency of these formulas is probably low, but it was fun working them out. oeldere's are probably more efficient with the helper columns, but here you are.

I ran these on your test sheet. They were written in cells I2:M2.
I2, K2,L2 and M2 are all array formulas which means they must be entered with Control + Shift + Enter, not just Enter.
The formula in J2 is just a regular formula, use Enter.

I2:

Formula:

=INDEX($C$3:$C$17,MATCH(MIN(ABS((IF($A$3:$A$17=H4,$C$3:$C$17,999)-$I$1))),ABS((IF($A$3:$A$17=H4,$C$3:$C$17-$I$1,999))),0))

J2:

Formula:

=ABS($I$1-$I4)

K2:
This formula can be copied into L2 and M2.

Formula:

=INDEX(D$3:D$17,MATCH($H4 & "|" & $I4,$A$3:$A$17 & "|" & $C$3:$C$17,0))

Good Luck.