Case-sensitive lookup in Excel

Why a normal lookup returns the wrong row

Short links, Base64 IDs, some product codes and some part numbers treat xK7p and Xk7P as different values. Excel's lookup functions do not: they compare text without regard to case and return the first match they find.

You want the destination for Xk7P (row 3). VLOOKUP stops at row 2, xK7p, because to VLOOKUP it is the same code. No error appears, so the wrong answer goes unnoticed.

Method 1: XLOOKUP with EXACT (Excel 365 / 2021)

EXACT(text1, text2) returns TRUE only when both texts match character for character, including case. Given a whole range, EXACT(A2:A8,E2) returns a list of TRUE and FALSE values, one per row. XLOOKUP then looks for TRUE in that list and returns the matching destination.

The full formula is =XLOOKUP(TRUE,EXACT(A2:A8,E2),B2:B8,"No exact match"). The fourth argument is the text to show when no code matches exactly. This is the clearest version to read later, so pick it if everyone who opens the file has Excel 365 or 2021.

In Excel 2019 and earlier, use =INDEX(B2:B8,MATCH(TRUE,EXACT(A2:A8,E2),0)) and confirm it with Ctrl+Shift+Enter, because older versions need that to treat it as an array formula.

Method 2: FIND with a length check

FIND is case-sensitive (SEARCH is not). FIND(E2,A2:A8) gives a position number where the code appears with the same case, and an error where it does not. ISNUMBER turns that into TRUE or FALSE. Adding LEN(A2:A8)=LEN(E2) rules out longer codes that merely contain E2, so only whole, exact matches remain.

Multiplying the two tests gives 1 for an exact match and 0 otherwise, and XLOOKUP looks for the 1. It returns the same result as the EXACT version and is handy if you already use FIND elsewhere.

Count or sum with exact case

COUNTIF and SUMIF ignore case too. Total clicks for "xk7p" with SUMIF and you get four different links added together. Move the same exact-case test into SUMPRODUCT and multiply it by the clicks.

With EXACT the version is =SUMPRODUCT(--EXACT(A2:A8,"xK7p"),C2:C8). The FIND version below gives the same total.

Check that two cells match exactly

The = operator is not case-sensitive either: =A2=E2 returns TRUE for xK7p and Xk7P. To compare two cells with case, use =EXACT(A2,E2), or the FIND and LEN pair in an IF when you want a label.

This is useful for spotting codes that were retyped with the wrong case, a common cause of broken short links after someone "tidies up" a list.

Which method to pick

Use XLOOKUP with EXACT in Excel 365 or 2021: it reads like the problem. Use INDEX/MATCH with EXACT for older versions. The FIND and LEN test does the same job; drop the LEN part when you want a case-sensitive "contains" test instead of a whole-cell match.

Other tools to know: Ctrl+F has a Match case box under Options. Data > Remove Duplicates and UNIQUE are not case-sensitive, so they treat xK7p and XK7P as duplicates. Power Query merges are case-sensitive by default, which can be what you want for large lists.