If your data is case sensitive, you may have issues finding correct matches with lookup functions. This How to make case sensitive match article shows you how to combine MATCH and EXACT functions to achieve case sensitive matching.

Syntax

{ =MATCH(TRUE, EXACT(search value, search array), 0) }

Steps

1. Start with =MATCH( function
2. Type TRUE, to search for matched values
3. Continue with EXACT( function
4. Select the reference that contains the search value E3,
5. Select the reference that contains the range to be searched $C$3:$C$5
6. Type ) to close the EXACT function
7. Add 0 to ensure exact match
8. Type ) to close the MATCH function and complete the formula
9. Click Ctrl + Shift + Enter instead of Enter to make it an array

How

The MATCH function is not a case sensitive function like the other lookup functions in Excel. However, with the EXACT function, we can make it to handle case sensitive match. The EXACT function compares 2 strings and returns a Boolean value if they are "exactly" the same or not. The EXACT function is case sensitive. There are 2 tricks here:

1. To make the EXACT function to return an array which is handled automatically if it is an array formula.
2. To search for TRUE values in the array that is returned by the EXACT function

Let's analyze the formula.

EXACT(E3,$C$3:$C$5) formula returns {FALSE;TRUE;FALSE} array for "CHARMANDER" value. By defining the formula as an array formula we ensure that EXACT will evaluate each cell in $C$3:$C$5 range.

It is then easy to find the exact match using MATCH among TRUE/FALSE values.

=MATCH(TRUE,EXACT(E3,$C$3:$C$5),0)