By default, Excel isnotcase-sensitive.
This means that standard lookup functions likeVLOOKUP,XLOOKUP, andINDEX and MATCHare also not case-sensitive.
These formulas will simply return thefirstmatch, ignoring upper and lower case.
The article below explains how to use this approach with INDEX and MATCH, and XLOOKUP.
EXACT function
TheEXACT functioncompares two text strings in a case-sensitive fashion.
Otherwise, EXACT returns FALSE.
For example:
If we use the EXACT function on arangeof values, we will get back multiple results.
Notice the only TRUE value is in the 5th position.
This corresponds to cell B9, which contains “JILL SMITH”.
Thelookup_arrayis created by the EXACT function.
MATCH then returns 5, since the only TRUE in the array is at the fifth position.
Note: this is anarray formulaand must be entered with Control + Shift + Enter inolder versions of Excel.
Note: For a more detailed example of a case-sensitive XLOOKUP formula,see this page.
you’re free to use INDEX to retrieve individual values, or entire rows and columns.
MATCH supports approximate and exact matching, andwildcards(* ?)
XLOOKUP supports approximate and exact matching, wildcards (* ?)
for partial matches, and lookups in vertical or horizontal ranges….
