Lookup entire row

Lookup entire row

Generic formula 

=INDEX(data,MATCH(value,array,0),0)

Related formulas 

Lookup entire column

Sum entire column

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.

Lookup entire row

Generic formula 

=INDEX(data,MATCH(value,array,0),0)

Related formulas 

Lookup entire column

Sum entire column

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.