# XLOOKUP lookup row or column

=XLOOKUP(value,headers,data) // lookup row

XLOOKUP can be used to lookup and retrieve rows or columns. In the example shown, the formula in H5 is:

=XLOOKUP(H4,C4:F4,C5:F8)

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.

*beta function*, available only through the Office Insiders program.

### How this formula works

One of the nice benefits of XLOOKUP is it can easily return entire rows or columns as a lookup result. This can be done also with INDEX and MATCH, but the syntax is more complex.

In the example shown, we want to retrieve all values associated with Q3. The formula in H5 is:

=XLOOKUP(H4,C4:F4,C5:F8)

- 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:

=XLOOKUP(B10,B5:B8,C5:F8)

- 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.

## 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.