This formula is an array formula and must be entered with Control + Shift + Enter.
How the formula works
Since MATCH alone isn't case sensitive, we need a way to get Excel to compare case. The EXACT function is the perfect function for this, but the way we use it is a little unusual, because we need to compare one cell to a range of cells.
where F4 contains the lookup value, and B3:B102 is a reference to the lookup column (First names). Because we are giving EXACT an array as a second argument, we will get back an array of TRUE false values like this:
This is the result of comparing the value in B4 every cell in the lookup column. Wherever we see TRUE, we know we have an exact match that respects case.
Now we need to get the position (i.e. row number) of the TRUE value in this array. For this, we can use MATCH, looking for TRUE and set in exact match mode:
It's important to note that MATCH will always return the first match if there are duplicates, so if there happens to be another exact match in the column, you'll only match the first one.
Now we have a row number. Next, we just need to use INDEX to retrieve the value at the right row and column intersection. The column number in this case is hard-coded as 3, since the named range data includes all columns. The final formula is:
Case sensitive lookups in Excel By default, standard lookups in Excel are not case-sensitive. Both VLOOKUP and INDEX/MATCH will simply return the first match, ignoring case. A direct way to workaround this limitation, is to use an array formula...
The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to...
MATCH is an Excel function used to locate the position of a lookup value in a row, column, or table. MATCH supports approximate and exact matching, and wildcards (* ?) for partial matches. Often, the INDEX...
The Excel EXACT function compares two text strings, taking into account upper and lower case characters, and returns TRUE if they are the same, and FALSE if not. EXACT is case-sensitive.
Excel Formula Training
Formulas are the key to getting work done in Excel. In this step-by-step 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. Start building valuable skills with Excel formulas today. Learn more.
Just a brief view of your website, and one specific idea - a "helper" value column - allowed me to do in less than two hours something I had been going down dead-ends with for two days. Thank you, thank you, thank you! -Roberta