How to nest INDEX and Match into IF statements?

Hi, welcome to the forum

Please upload a sample of your workbook, not a picture of your data. Pictures are pretty much impossible to edit, and no-one wants to re-type your data for you
Also, not all members can upload picture files (Company firewalls and stuff) - and, depending on what browser is being used, some pics dont even show up on the forum

Your workbook should show a small desensitized example of the data you are working with and a manual mockup of the expected results.

I used helpcells to get the result.

See the yellow cells for the helpcells.

See the green cells for the result.

I used index / match after I added column A and C together in the current data.


P.s. you don't added what value should be showing if the differance in absolut way is the same.

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.