By default, Excel isnotcase-sensitive and this applies to standard lookup formulas likeVLOOKUP,XLOOKUP, andINDEX and MATCH.
These formulas will simply return thefirstmatch, ignoring case.
We need a way to get Excel to compare case.
TheEXACT functionis perfect for this task, but the way we use it is a little unusual.
Instead of comparing onetext valueto another, we compare one text value tomanyvalues.
Background reading
This article assumes you are familiar with Excel Tables and INDEX and MATCH.
A TRUE value indicates an exact, case-sensitive match.
A FALSE value means no exact match.
Based on the results above, we have an exact match at row 6 of the table.
With INDEX and MATCH formulas, this is done with theMATCH function.
When EXACT delivers this array to MATCH, the original data is no longer available.
The MATCH function returns 6.
Note: MATCH will always return the first match if there are duplicate lookup values.
The MATCH function returns the number 6 forrow_num, as explained above.
Thecolumn_numargument is hardcoded as 3, sincearrayis given asdata, which includes all three columns.
The result is returned directly to XLOOKUP as thelookup_array.
Thelookup_valueis set to TRUE, andreturn_arrayis the “Department” column.
For a more detailed explanation of XLOOKUP,see this example.
it’s possible for you 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….
