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?

Jan 30, 2023 in Others 540 views

## 1 answer to this question.

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)))`
• 63,700 points

## Excels INDEX with MATCH for multiple criteria

How to Use INDEX MATCH With Multiple Criteria ...READ MORE

## Excel - IF combined with Index Match

Try: =IFERROR(IF(ISNUMBER(O2),VLOOKUP(A2,Sheet2!\$C\$2:\$E\$10,3,FALSE),""),"") =IFERROR(IF(ISNUMBER(O2),INDEX(Sheet2!\$E\$2:\$E\$10,MATCH(A2,Sheet2!\$C\$2:\$C\$10,0)),""),"") where the first "" means that O2 is not a number, ...READ MORE

## Conditional formatting based on value with offset

Apart than the altered cell, Conditional Formatting ...READ MORE

## Conditional Formatting (IF not empty)

Use Conditional formatting with the option "Formula ...READ MORE

## Conditional format based on external cell value

Use a rule with the formula =\$Q6="Yes" and apply ...READ MORE

## Excel VBA Selecting Multiple columns with last row and apply Condition Format

Try this: Sub Test() Dim ...READ MORE