which returns 25, the height in column B for model H in row 12. In the example shown, we are looking for the weight associated with Model H in row 12. The formula in H6 is:

The lookup_value comes from cell H4 The lookup_array is the range E5:E14, which contains Model The return_array is B5:B14, which contains Weight 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)

Lookup multiple values

XLOOKUP can return more than one value at a time from the same matching record. The formula in cell G9 is: which returns the Height, Weight, and Price of Model H in an array that spills into the range G9:H9. The only difference from the formula above is that return_array is entered as a range that contains more than one column, B5:D14.

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.