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. Working from the inside-out, EXACT is configured to compare the value in E5 against names in the range B5:B14: 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: This array is returned directly to the MATCH function as the lookup_array like this: 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 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: Notice the lookup value and lookup array is set up just like the MATCH function above. After EXACT runs, we have: and XLOOKUP returns the 5th item from the range C5:C14 (39) as a final result.
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.
