Conditional formatting with Index Match 2 conditions vertical horizontal

Question

I have this data set in Excel (google sheet) Sheet1

And these targets by month by the city in Sheet2

I would like cellS AI3 to AK5 to highlight if the objective of Sheet 2, for the specific city and month is not reached.

I thought that writing this in the conditional format box would work but it doesn't

 =AI3<INDEX(Sheet2!D:D;MATCH($A3&MONTH(AI$2);Sheet2!$A:$A&Sheet2!$C:$C;0)))

Any idea?

Answer 1

Use FILTER to search using two or more different criteria. Despite your title, it appears like both variables in this situation are moving in the same direction. Try using:

=AI3<FILTER(Sheet2!D:D;INDIRECT("Sheet2!$A:$A");$A3;INDIRECT("Sheet2!$C:$C");MONTH(AI$2)))