Conditional formatting with Index Match 2 conditions vertical horizontal

0 votes

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

![enter image description here

And these targets by month by the city in Sheet2

![enter image description here

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 by Kithuzzz
• 38,010 points
540 views

1 answer to this question.

0 votes

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)))
answered Jan 30, 2023 by narikkadan
• 63,700 points

Related Questions In Others

0 votes
1 answer

Excels INDEX with MATCH for multiple criteria

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

answered Nov 8, 2022 in Others by gaurav
• 23,260 points
619 views
0 votes
1 answer

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

answered Nov 17, 2022 in Others by narikkadan
• 63,700 points
526 views
0 votes
1 answer

Excel Count if with index match

Try this: Countif(Index(rangeoflookupvalues,,match(Columnvaluetobe ...READ MORE

answered Dec 10, 2022 in Others by narikkadan
• 63,700 points
1,557 views
0 votes
1 answer

Conditional formatting based on value with offset

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

answered Apr 4, 2023 in Others by Kithuzzz
• 38,010 points
414 views
0 votes
1 answer

Conditional Formatting (IF not empty)

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

answered Oct 22, 2022 in Others by narikkadan
• 63,700 points
439 views
0 votes
1 answer

Format an Excel Sheet in Python

The below code will help you refer ...READ MORE

answered Nov 6, 2022 in Others by narikkadan
• 63,700 points
1,157 views
0 votes
1 answer

Conditional format based on external cell value

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

answered Feb 16, 2023 in Others by narikkadan
• 63,700 points
430 views
0 votes
1 answer
0 votes
1 answer

XLOOKUP Formula Issue Horizontal & vertical lookup

Actually, your ranges are wrong. To correct ...READ MORE

answered Nov 5, 2022 in Others by narikkadan
• 63,700 points
427 views
0 votes
1 answer

Avoiding index & match #N/A value

Use IFERROR to set an alternative value if the ...READ MORE

answered Nov 14, 2022 in Others by narikkadan
• 63,700 points
233 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP