Quick, clean, and to the point

Lookup entire row

Excel formula: Lookup entire row
Generic formula 

To lookup and retrieve an entire row, you use the INDEX and MATCH functions. In the example shown, the formula used to look up all values for the Central region is:


The core of this formula is to use the MATCH function to identify the row index, and the INDEX function to retrieve the entire row by setting the column number to zero. Working from the inside out, MATCH is used to get the row index like this:


The lookup value "Central" comes from H5, the array is the range B5:B8, and zero is used to force an exact match. The MATCH function returns 3 as a result, which goes into the INDEX function as the row number:


With the range C5:F8 for array, and 3 for row number, the final step is to supply zero for column number. This causes INDEX to return all of row 3 as the final result, in an array like this:


Processing with other functions

Once you retrieve an entire row of data, you can feed that row into functions like SUM, MAX, MIN, AVERAGE, LARGE, etc. for further analysis. For example, to get the smallest value in the Central region, you could use:


To sum all values in the Central region:


Multi-cell array formula

To return results to more than one cell, enter as a multi-cell array formula.

In the Dynamic Array version of Excel, when INDEX returns an entire row, row values will automatically spill onto the worksheet.

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.