Lookup entire row

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:

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

{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. for further analysis. For example, to get the smallest value in the Central region, you could use:

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

To sum all values in the Central region:

=SUM(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.

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