Case sensitive lookup
To perform a case-sensitive lookup, you can use the EXACT function together with INDEX and MATCH. In the example show, the formula in F5 is:
which returns 39, the age of "JILL SMITH".
Working from the inside-out, EXACT is configured to compare the value in E5 against names in the range B5:B14:
EXACT(E5,B5:B14) // returns array of results
The EXACT function performs a case-sensitive comparison and returns TRUE or FALSE as a result. Only values that match exactly will return TRUE. Because we are checking the name in E5 ("JILL SMITH") against all ten names in the range B5:B14, we get back an array of ten TRUE and FALSE values like this:
This array is returned directly to the MATCH function as the lookup_array like this:
With a lookup value of TRUE, MATCH returns 5, since the only TRUE in the array is at the fifth position. Notice also that match_type is set to zero (0) to force an exact match.
The MATCH function returns a result directly to the INDEX function as the row number, so we can now rewrite the formula like this:
=INDEX(C5:C14,5) // returns 39
INDEX returns the age in the fifth row, 39, as a final result.
In Excel 365, the XLOOKUP function can be configured to perform a case-sensitive lookup in a similar way like this:
Notice the lookup value and lookup array is set up just like the MATCH function above. After EXACT runs, we have:
and XLOOKUP returns the 5th item from the range C5:C14 (39) as a final result.