INDEX & MATCH with multiple criteria

[mwelshman]

Hi,

I have an INDEX & MATCH problem I'm having trouble solving. The first part works ok, shown below; return a value from B4:B49 where J4 is greater than or equal to C4:C49, J4 is less that or equal to D4:D49, J3 is greater than or equal to E4:E49, J3 is less that or equal to G4:G49.

I'd like to add in another criteria where J3 is closest to F3:F49. It doesn't matter whether it's greater or less than, just that it's the closest.

=INDEX(B4:B49,MATCH(1,(J4>=C4:C49)*(J4<=D4:D49)*(J3>=E4:E49)*(J3<=G4:G49),0))

Can anyone help me which formula to use/add into my existing formula?

 

[Chrisdontm]

Try this:

=INDEX($B$4:$B$49,MATCH(AGGREGATE(15,6,ABS($J$3-(INDEX(((J4>=C4:C49)*(J4<=D4:D49)*(J3>=E4:E49)*(J3<=G4:G49))*$F$4:$F$49,))),1),ABS($J$3-$F$4:$F$49),0))

( It is an array formula, so enter it with; Ctrl. + Shft. + Enter )

 

[Aladin Akyurek]

@ Chrisdontm

Do we really need AGGREGATE and inner INDEX as the latter cannot circumvent control+shift+enter?

More importantly, the match range you have is underspecified which can lead to a wrong outcome. The following does have the correct match-rage for the MATCH bit:

=INDEX(B4:B49,MATCH(MIN(ABS(J3-IF(J4>=C4:C49,IF(J4<=D4:D49,IF(J3>=E4:E49,IF(J3<=G4:G49,F4:F49)))))),ABS(J3-IF(J4>=C4:C49,IF(J4<=D4:D49,IF(J3>=E4:E49,IF(J3<=G4:G49,F4:F49))))),0))

which must be confirmed with control+shift+enter, not just enter.

 

[mwelshman]

This is excellent and it almost works. Thanks Aladin Akyurek. However I need it to error or not return a result if none of the criteria are met. Currently this formula returns the result in the first row if my original criteria below aren't met. Is this able to built into the IF statement somewhere?

(J4>=C4:C49)*(J4<=D4:D49)*(J3>=E4:E49)*(J3<=G4:G49)

 

[Aladin Akyurek]

This is excellent and it almost works. Thanks Aladin Akyurek. However I need it to error or not return a result if none of the criteria are met. Currently this formula returns the result in the first row if my original criteria below aren't met. Is this able to built into the IF statement somewhere?

(J4>=C4:C49)*(J4<=D4:D49)*(J3>=E4:E49)*(J3<=G4:G49)

Control+shift+enter, not just enter:

=IF(COUNTIFS(C4:C49,"<="&J4,D4:D49,">="&J4,E4:E49,"<="&J3,G4:G49,">="&J3),INDEX(B4:B49,MATCH(MIN(ABS(J3-IF((J4>=C4:C49)*(J4<=D4:D49)*(J3>=E4:E49)*(J3<=G4:G49),F4:F49))),ABS(J3-IF((J4>=C4:C49)*(J4<=D4:D49)*(J3>=E4:E49)*(J3<=G4:G49),F4:F49)),0)),NA())

If any of the conditions are not met, we get #N/A. If you want something different, just replace NA().

 

[mwelshman]

This works perfectly. Thank you!

 

[Aladin Akyurek]

This works perfectly. Thank you!

You are welcome.