Conditional formatting Excel results from cell, address functions

0
votes

I am using the cell, address, index, and match functions in Excel to return the address of the intersection of a row and column match. I can get the address correct using the following function, but I can't get it to format using conditional formatting with a formula. 

 =a2=CELL("address",INDEX(A2:E6,MATCH("g",A2:A6,0),MATCH("c",A2:E2,0)))

The data looks like this and the upper left corner is in cell A2.

    a    b    c    d
e   1    2    3    4
f   5    6    7    8
g   9    10   11   12
h   13   14   15   16

I want to enter a formula that colors the matching cell a certain color (the "11", in this example). How do I get this to work? If I just type =a2=$D$5 in the conditional formatting formula, it works, but I can't get it to work using the formula above.

1
excelconditional-formatting

1 Answers

4
votes

Try following. I tested and got correct formatting.

=CELL("address",A2)=CELL("address",INDEX($A$2:$E$6,MATCH("g",$A$2:$A$6,0),MATCH("c",$A$2:$E$2,0)))

enter image description here

This will also work if there is no duplicate values in range.

=A2=INDIRECT(CELL("address",INDEX($A$2:$E$6,MATCH("g",$A$2:$A$6,0),MATCH("c",$A$2:$E$2,0))))

Another way to do that

=CELL("address",A2)=ADDRESS(MATCH("g",$A$1:$A$6,0),MATCH("c",$A$2:$E$2,0))