This formula is an array formula and must be entered with Control + Shift + Enter.
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:
SUMPRODUCT is designed to work with arrays, which it multiplies, then sums. In this case, we are two arrays with SUMPRODUCT: B3:B8 and C3:C8. The trick is to run a test on the values in column B, then convert the resulting TRUE/FALSE values to 1's...
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, MATCH is combined...
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 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.