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.

Generic formula

=INDEX(range1,MATCH(TRUE,EXACT(A1,range2),0))

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.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.