Exceljet

Quick, clean, and to the point

Look up entire row

Excel formula: Look up entire row
Generic formula 
=XLOOKUP(value,column,data)
Summary 

To look up and retrieve an entire row, you can use a formula based on the XLOOKUP function. In the example shown, the formula in cell I5  is:

=XLOOKUP(H5,project,data)

where project (B5:B16) and data (C5:F16) are named ranges. With a lookup value of "Neptune" in cell H5, the result is all four quarterly values for the Neptune project, which spill into the range I5:L5.

Note: this problem can also be solved with INDEX and MATCH, as described below.

Explanation 

In this example, the goal is to look up and retrieve an entire row of values in a set of data. For example, when a value like "Neptune" is entered into cell H5, all values in the range C11:F11 should be returned. For convenience and readability, project (B5:B16) and data (C5:F16) are named ranges.

Although this example shows off the simplicity of the XLOOKUP function, it can also be solved with a straightforward INDEX and MATCH formula, as described below. It can also be solved with the FILTER function.

With XLOOKUP

With the XLOOKUP function, the solution is simple. In the example shown, the formula in I5 is:

=XLOOKUP(H5,project,data)

Here, lookup_value is H5 (which contains "Neptune"), lookup_array is project (B5:B16), and  return_array is data (C5:F16). In this configuration, XLOOKUP matches the 7th value in B5:B16, and returns the 7th column in C5:F16. In the dynamic array version of Excel, the 4 values in C11:F11 spill into the range I5:L5. If the value in H5 is changed to a different project, the formula will immediately recalculate and return a new column of values.

With FILTER

The FILTER function is designed to return multiple matching rows from a set of data, but it will work fine in this case as well. The syntax looks like this:

=FILTER(data,project=H5)

The result is the same as with the XLOOKUP formula, since there is only one project named "Neptune". However, if there were multiple rows with Neptune as the project, FILTER would return data for all of these rows. See this page for more details and examples.

With INDEX and MATCH

This problem can also be solved with an INDEX and MATCH formula like this:

=INDEX(data,MATCH(P5,project,0),0)

The gist of the solution is that the MATCH function is used to identify the correct row number in project, and the INDEX function retrieves the entire row in data when  column_num is set to zero(0). Working from the inside out, MATCH is used to get the row index like this:

MATCH(P5,project,0) // returns 7

With "Neptune" in H5, the MATCH function returns 7, since "Neptune" is the seventh value in project (B5:B16). MATCH returns this result directly to the INDEX function as the row_num argument, with array given as data, and column_num set to 0:

=INDEX(data,7,0)

This causes INDEX to return all 4 values in the seventh column of data as a final result. In the dynamic array version of Excel, these results will spill into the range I5:L5. In Legacy Excel, the values won't spill automatically and you will need to enter this formula as a multi-cell array formula.

Processing with other functions

Often, the purpose of looking up and retrieving an entire row of values is to feed those values into another function like SUM, MAX, MIN, AVERAGE, etc. To do this, simply nest the formula above inside the other function. For example,  you can get the sum, max, and average for Project Neptune like this:

=SUM(XLOOKUP(H5,project,data)) // get sum
=MAX(XLOOKUP(H5,project,data)) // get max
=AVERAGE(XLOOKUP(H5,project,data)) // get average

In each formula, the XLOOKUP returns all 4 values in the row to the outer function, which returns a single result. If the project in H5 is changed, the formula recalculates, and a new result is returned. The INDEX and MATCH and FILTER versions of the formula can be nested in the same way.

Author 
Dave Bruns

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.

Download 100+ Important Excel Functions

Get over 100 Excel Functions you should know in one handy PDF.