Conditional formatting based on values in another table

whitehawk81

Board Regular
Joined
Sep 4, 2016
Messages
52
Hi everyone!

I haven't posted any issues for a while, but this one really got me or I'm just getting older :)
Basically I would like to format the cells based on the result of a lookup value from another table.
I managed to apply the index match formula to return the value, that needs to be evaluated, but when I put it in the conditional formatting, it doesn't find anything.

The formula looks like this: ="INDEX(Table2;MATCH(J4;Table2[Change];0);1)=$X$3"

I also tried it with index/match multiple criteria array formula: ="INDEX(Table[Env];MATCH(1;($I5=Table[Planname])*(K$3=Table[Date]);0))=$X$3"

Could someone help me?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,213
Office Version
365
Platform
Windows
You need to remove the "s from around the formula, I suspect that you may need to change the structured references to cell references as well.
 
Last edited:

whitehawk81

Board Regular
Joined
Sep 4, 2016
Messages
52
Thank you! Both of your comments were right :) I managed to format the cells with the array formula.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
30,213
Office Version
365
Platform
Windows
Glad you sorted it & thanks for the feedback
 

Forum statistics

Threads
1,077,797
Messages
5,336,385
Members
399,079
Latest member
DeoW

Some videos you may like

This Week's Hot Topics

Top