Since all data in the C5:F8 is provided as the return_array XLOOKUP returns the range E5:E8 as a result, which spills into the range H5:H8. In the example shown, we want to retrieve all values associated with Q3. The formula in H5 is:
The lookup_value comes from cell H4, which contains “Q3” The lookup_array is the range C4:F4, which quarters in a header The return_array is C5:F8, which contains all data The match_mode is not provided and defaults to 0 (exact match) The search_mode is not provided and defaults to 1 (first to last)
XLOOKUP finds “Q3” as the second item in C4:F4 and returns the second column of the return_array, the range E5:E8.
Lookup row
In the example shown, XLOOKUP is also used to lookup a row. The formula in C10 is:
The lookup_value comes from cell B10, which contains “Central” The lookup_array is the range B5:B8, which lists regions The return_array is C5:F8, which contains all data The match_mode is not provided and defaults to 0 (exact match) The search_mode is not provided and defaults to 1 (first to last)
XLOOKUP finds “Central” as the third item in B5:B8 and returns the third row of the return_array, the range C7:F7.
Further processing with other functions
The results delivered by XLOOKUP can be handed off to other functions for further processing. For example, to add up all Q3 numbers, you can use the SUM function like this: which returns a single result, 503,250.
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.
