conditional formatting: is value in a table

Hi out there,

I have a column that I want to format conditionally as follows: If a cell in the same row contains a text (e.g. "dummy1") that is also in a table consisting of e.g. 12 colums, I want the cell to get red.

I can do this, if the "checking table" consists of only one column, but not for more.

Any suggestions?

Hi,

select the cell and enter conditional format with FormulaIs

=match($A$1,$B2:$Z2,0)>0

where A1 contains your search term. The same formula can also be used to conditionally format several cells in the same row, not just in one column.

Adjust ranges to suit.

hth

Try to test your formula in a worksheet before you commit it to conditional formatting. Put it in any cell. The result should be TRUE or FALSE.

MATCH() will only check a one column or one row range, and will return #NA if you feed it a multi-row, multi-column range. As an alternative you may use

=SUMPRODUCT(($J$2:$V$100=F2)*1)>0

Put the above formula into a cell and play withe the ranges until it fits, i.e. returns TRUE or FALSE based on your human logic, then copy the formula into the FormulaIs box of conditional formatting.

hth

The sumproduct function multiplies words?

uhmmm, not really. But it's very powerful. Read up on it in this brilliant article:

http://www.xldynamic.com/source/xld.SUMPRODUCT.html

cheers

I'm not sure you really need SUMPRODUCT, why not use COUNTIF ? The latter is more efficient and Conditional Formatting to quote Charles Williams is "super volatile"... so avoiding SUMPRODUCT where possible may prove worthwhile.

=COUNTIF($J$2:$V$100,$F2)

Originally Posted by cunner

I don't know what the ">0" in the end of your formula does...

in reality this is not required based on the following points:

-- Conditional Formatting concerns itself only with TRUE/FALSE output.

-- in XL only 0 equates to FALSE (thus all other numbers are in effect TRUE)

-- Errors will simply be evaluated as FALSE

So in essence should the MATCH have found something it would return an integer which would thus equate to TRUE, if it did not it would return an Error (#N/A) which thus equates to FALSE.

If errors were not handled in this manner - as is the case should you use the formula within a cell - then you would use an ISNUMBER test rather than a >0 test given a MATCH will never return 0.

=ISNUMBER(MATCH(...))

which would return TRUE (found) or FALSE (not found)

=MATCH(...)>0

would return TRUE (found) or #N/A (not found) as would

=MATCH(...)

but as discussed - in Conditional Formatting you don't need the additional test because Errors are treated as False by default so the last example would work without issue.