Exceljet

Quick, clean, and to the point

Case sensitive lookup

Excel formula: Case sensitive lookup
Generic formula 
=INDEX(range1,MATCH(TRUE,EXACT(A1,range2),0))
Summary 

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:

=INDEX(C5:C14,MATCH(TRUE,EXACT(E5,B5:B14),0))

which returns 39, the age of "JILL SMITH".

Note: this is an array formula and must be entered with Control + Shift + Enter, except in Excel 365.

Explanation 

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 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:

{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE}

This array is returned directly to the MATCH function as the lookup_array like this:

MATCH(TRUE,{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE},0)

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.

Because using the EXACT function like this is an array operation, the formula is an array formula and must be entered with Control + Shift + Enter, except in Excel 365.

With XLOOKUP

In Excel 365, the XLOOKUP function can be configured to perform a case-sensitive lookup in a similar way like this:

=XLOOKUP(TRUE,EXACT(J5,B5:B14),C5:C14,"na",0)

Notice the lookup value and lookup array is set up just like the MATCH function above. After EXACT runs, we have:

=XLOOKUP(TRUE,{FALSE;FALSE;FALSE;FALSE;TRUE;FALSE;FALSE;FALSE;FALSE;FALSE},C5:C14,"na",0)

and XLOOKUP returns the 5th item from the range C5:C14 (39) as a final result.

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.