Forum Discussion
Solved
Conditional Formating, a single reference point
Hi Is there a way to reference a list in conditional formatting, for example, I want have a list of key words or abbreviations like ERT, BBC, Motor-OIL. The list will expand as time goes on. So...
- MATCH tries to find an item in a list. If successful, it returns the position within that list where the item was found. If not successful, it returns an error: #N/A.
ISERROR simply returns TRUE if a formula returns an error.
NOT reverses a boolean value so TRUE becomes FALSE and vice versa.
So NOT(ISERROR(MATCH(..))) returns TRUE if a match was found, which then tells Excel to color the cell.
That is great, sorry its fabulous works nicely. Are able to explain to me what the formula is doing? I will admit Match(), and also Index(), are some of the functions I have avoided as they look too complicated and I have not seen NOT() before.
Chris
MATCH tries to find an item in a list. If successful, it returns the position within that list where the item was found. If not successful, it returns an error: #N/A.
ISERROR simply returns TRUE if a formula returns an error.
NOT reverses a boolean value so TRUE becomes FALSE and vice versa.
So NOT(ISERROR(MATCH(..))) returns TRUE if a match was found, which then tells Excel to color the cell.
ISERROR simply returns TRUE if a formula returns an error.
NOT reverses a boolean value so TRUE becomes FALSE and vice versa.
So NOT(ISERROR(MATCH(..))) returns TRUE if a match was found, which then tells Excel to color the cell.
ISERROR simply returns TRUE if a formula returns an error.
NOT reverses a boolean value so TRUE becomes FALSE and vice versa.
The old double-negative-makes-a-positive trick. Nicely done! 🙂
