Exceljet

Quick, clean, and to the point

Lookup entire row

Excel formula: Lookup entire row
Generic formula 
=INDEX(data,MATCH(value,array,0),0)
Explanation 

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:

=INDEX(C5:F8,MATCH(H5,B5:B8,0),0)

How this formula works

The gist: use MATCH to identify the row index, then INDEX 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:

MATCH(H5,B5:B8,0)

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:

=INDEX(C5:F8,3,0)

With the range C5:F8 for array, and 3 for column 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:

{116000,129250,127250,142500}

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. to retrive information of interest. For example, to get the smallest value in the Central region, you could use:

=MIN(INDEX(C5:F8,MATCH(H5,B5:B8,0),0))

Multi-cell array formula

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

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.