Highlight intersection in a table using conditional formatting


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!
 
Ad

Advertisements

T

T. Valko

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

I see your table contains duplicate values. What does your INDEX/MATCH
formula look like?
 
Ad

Advertisements

G

geoff_ness

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!

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top