Learn how to use XLOOKUP to return entire rows or columns as a lookup result without INDEX and MATCH functions.
XLOOKUP is a real swiss-knife in Excel. One of the advantages of the function is that you can display the return value (lookup result) in entire rows and columns. However, if you are working with a different version of Excel365, you can use the least user-friendly INDEX and MATCH combination.
General Formula to lookup row
=XLOOKUP(lookup_value, lookup row, data)
In the example, we want to return all corresponding values with “Colorado”, so we use the function to look up a row.
Formula:
=XLOOKUP(B10, B4:B7, C4:F7)
Take a look at the arguments:
- lookup_value is “Colorado”
- lookup_array is the range B4:B7 that contains names
- return_array is C4:F7, which contains all sales data
- if_not_found argument is not provided
- match_mode is exact match (default = 0)
- search_mode is search first to last (default = 1)
Formula Example to lookup column
In the previous example, we demonstrated how to lookup a row. We want to return all corresponding values with March, so we use the function to look up a column.
The formula in I3 is:
=XLOOKUP(I3, C3:F3, C4:F7)
- lookup_value is “Mar”
- lookup_array is the range C3:F3 that contains names
- return_array is C4:F7
We leave the last three optional arguments by default.
Excel evaluates the formula; the function lookup the first matching record for “Mar” and returns the third column as result, the E4:E7 range.
XLOOKUP will spill the matching values (E4:E7) into a return_array in the range I4:I7. We need to enter the formula in cell I4, which will automatically spill down to cell I7.
