When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each cell in B4:G12...

At the core, this is an INDEX and MATCH formula, with the EXACT function used inside MATCH to perform a case-sensitive match.
Working from the inside-out, EXACT is configured to compare the value in E5 against names...

By itself, the XLOOKUP function is not case-sensitive. A lookup value of "RED" will match "red", "RED", or "Red". We can work around this limitation by constructing a suitable lookup array for XLOOKUP with a logical...

This formula uses the EXACT formula to compare a range of cells to a single value:
=EXACT(B5:F5,B5)
Because we give EXACT a range of values in the first argument, we get back an array result containing TRUE FALSE...

The EXACT function takes two arguments, text1 and text2. When text1 and text2 match exactly (respecting case), EXACT returns TRUE. Otherwise, EXACT returns FALSE:
=EXACT("ayako","ayako") //...

The MATCH function has a limit of 255 characters for the lookup value. If you try to use longer text, MATCH will return a #VALUE error.
To workaround this limit you can use boolean logic and the LEFT, MID, and EXACT...

Data validation rules are triggered when a user adds or changes a cell value.
The UPPER function changes text values to uppercase, and the EXACT function performs a case-sensitive comparison.
The AND function takes...

If you need to compare two text strings in Excel to determine if they're equal, you can use the EXACT function. For example, if you want to compare A2 with B2, use:
=EXACT(A2,B2)
If the two strings are identical,...

Data validation rules are triggered when a user adds or changes a cell value.
In this formula, the LEFT function is used to extract the first 3 characters of the input in C5.
Next, the EXACT function is used to...

Since MATCH alone isn't case sensitive, we need a way to get Excel to compare case. The EXACT function is the perfect function for this, but the way we use it is a little unusual, because we need to compare one cell to...

The AND function is designed to evaluate multiple logical expressions, and returns TRUE only when all expressions are TRUE.
In this case the we simply compare one range with another with a single logical expression...

When you use a formula to apply conditional formatting, the formula is evaluated relative to the active cell in the selection at the time the rule is created. In this case, the rule is evaluated for each of the 20...

SUMPRODUCT is designed to work with arrays, which it multiplies, then sums.
In this case, we are two arrays with SUMPRODUCT: B3:B8 and C3:C8. The trick is to run a test on the values in column B, then convert the...

Note: Excel contains many built-in "presets" for highlighting values with conditional formatting, including a preset to highlight cells that equal a specific value. However, for more flexibility, you can use your own...