Highlight intersection in a table using conditional formatting

  • Thread starter Thread starter Des
  • Start date Start date
D

Des

I used the index-match function to return a value from a source table (see
below). Now I want to highlight that specific value from the source table
using conditional formatting.

Here's the Source Table:
A B C D
1 5.5 6.4 7.2 8.1
2 3.5 7.8 6.4 5.5
3 6.7 7.2 1.1 6.4

Row Col Value
2 C 6.4 --> (used index-match to return value)

How can I use conditional formatting to highlight the return vaule "6.4" on
the source table itself (which is the intersection between row 2 and col C)?

Thanks in advance!
 
Here's the Source Table:
A B C D
1 5.5 6.4 7.2 8.1
2 3.5 7.8 6.4 5.5
3 6.7 7.2 1.1 6.4
Click to expand...

I see your table contains duplicate values. What does your INDEX/MATCH
formula look like?
 
I used the index-match function to return a value from a source table (see
below). Now I want to highlight that specific value from the source table
using conditional formatting.

Here's the Source Table:
     A      B      C      D
1  5.5   6.4    7.2   8.1
2  3.5   7.8    6.4   5.5
3  6.7   7.2    1.1   6.4

Row   Col   Value
  2       C      6.4 --> (used index-match to return value)

How can I use conditional formatting to highlight the return vaule "6.4" on
the source table itself (which is the intersection between row 2 and col C)?

Thanks in advance!
Click to expand...

Assuming it's the address that's significant rather than the value in
the cell (as Biff pointed out there are duplicate values so your
conditional formatting would highlight all cells containing 6.4
otherwise), the following worked for me:
naming the row cell inpRow, and the column cell inpCol for
convenience, select the table and in the conditional formatting dialog
use "Formula is" and set it equal to:
=(ADDRESS(ROW(),COLUMN(),4)=inpCol&inpRow)
and change the format from there. This will highlight only the cell at
the intersection of inpRow and inpCol.

If, on the other hand, the value is what's significant, select the
table, and use:
=(A1=INDIRECT(inpCol&inpRow))
where A1 is the active cell, and this will highlight all the cells
with the same value.

HTH
 
Back
Top