Count IF and Conditional Formating

[bdolph]

I am looking for a way to:

conditionally format a cell in row A by looking at the text in the top
of row A then search the top cells in a series of other columns for a
match in the top cell, and then go down in that row to the same row
down as in row A; if there is something in that cell then color format
the original cell.

Thanks Bob

 

[Myrna Larson]

Let's say the column headers to be searched are $K$1:$P$1, and a cell in
column A (say A7) is selected when you set up the formatting. Here's a formula
that will work:

=NOT(ISBLANK(OFFSET($K$1,ROW()-1,MATCH(A$1,$K$1:$P$1,0)-1)))

or, if the data is numeric and 0 isn't a legal value:

=OFFSET($K$1,ROW()-1,MATCH(A$1,$K$1:$P$1,0)-1)<>0

 

[Myrna Larson]

Here's a simpler formula which assumes that the active cell is in column A.
The "trick" is to just make the number 1000 big enough to include the maximum
possible number of rows. IOW, if you have data going down to row 2872, the
$1000 would have tobe $2872 or greater.

=NOT(ISBLANK(HLOOKUP(A$1,$K$1:$P$1000,ROW(),0)))