Using Index and Match in Conditional Formatting

[frenne]

I am trying to find a solution to colorize referral cells using conditional formatting.

I have two worksheets listing names in columns and dates in a row. The names are not sorted the same way in the different sheets.


I want one cell in one sheet to colorized if a correlating cell with matching name and date in the other sheet is more than 0.


The custom formula I have tried to use is:


=(INDEX('Resource Planning'!$A$3:$NG$100;MATCH($B33;'Resource Planning'!$A$5:$A$100);MATCH(GU$3;'Resource Planning'!$G$3:$NG$3)))


The other sheet is called Resource, Row 3 is Date and Column B is names and Column A in the sheet Resource.


The formula works by itself in a cell, but not in condtional formatting. Has anyone a smart solution for this?

 

[Marcelo Branco]

The array argument in INDEX is A3:NG100 but lookup_array in the first MATCH is A5:A100.

I think this may be the cause of the error.

M.

 

[frenne]

I tried changing it to A3 but I still get an error when I am trying to apply it in the conditional formating formula field.
It works fine in a cell, but not in the conditional formation.

Thanks,

 

[Comfy]

Your formula will just return the Value from the found cell rather than TRUE or FALSE.

Try this:

=(INDEX('Resource Planning'!$A$4:$NG$100;MATCH($B33;'Resource Planning'!$A$4:$A$100);MATCH(GU$3;'Resource Planning'!$G$3:$NG$3)))>0

Just noticed that your vertical array started in the same row as your Horizontal array (Headers?) changed both to start in A4.

 

[Marcelo Branco]

oops...

The second MATCH, also, is not correct

Try

=INDEX('Resource Planning'!$A$3:$NG$100;MATCH($B33;'Resource Planning'!$A$3:$A$100,0);MATCH(GU$3;'Resource Planning'!$A$3:$NG$3,0))

Hope this helps

M.

 

[Marcelo Branco]

It seems that you use ; as argument separator

So try this
=INDEX('Resource Planning'!$A$3:$NG$100;MATCH($B33;'Resource Planning'!$A$3:$A$100;0);MATCH(GU$3;'Resource Planning'!$A$3:$NG$3;0))

M.

 

[frenne]

The last forumula works great (except in a cell. But as soon as I add it as condtional formatting it says it contains errors.

=INDEX('Resource Planning'!$A$3:$NG$100;MATCH($B33;'Resource Planning'!$A$3:$A$100;0);MATCH(GU$3;'Resource Planning'!$A$3:$NG$3;0))>0

Should these types of formulas not work with conditional formatting?

 

[Marcelo Branco]

What Excel version are you using?

M.

 

[frenne]

Version 14 on a Mac.

I got it working now, but it gives me the wrong results.
Any other formulas I can use that might be even better?

Thanks,

 

[Marcelo Branco]

hmm...i'm afraid you have to use a formula exactly like this.

I have no experience with Excel for Mac but i think the formula should give you the correct results. Please, check the ranges.

It worked perfectly for me. (Excel 2010)

M.