Lookup entire column

Lookup entire column

Generic formula 

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

Related formulas 

Lookup entire row

Sum entire column

Explanation

To lookup and retrieve an entire column, you can use a formula based on the INDEX and MATCH functions. In the example shown, the formula used to lookup all Q3 results is:

=INDEX(C5:F8,0,MATCH(I5,C4:F4,0))

How this formula works

The gist: use MATCH to identify the column index, then INDEX to retrieve the entire column by setting the row number to zero.

Working from the inside out, MATCH is used to get the column index like this:

MATCH(H5,C4:F4,0)

The lookup value "Q3" comes from H5, the array is the headers in C4:F4, 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 column number:

=INDEX(C5:F8,0,3)

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

{121250;109250;127250;145500}

Processing with other functions

Once you retrieve an entire column of data, you can feed that column into functions like SUM, MAX, MIN, AVERAGE, LARGE, etc. to retrive information of interest. For example, to get the MAX of column 3, you could use:

=MAX(INDEX(C5:F8,0,MATCH(I5,C4:F4,0)))

 

Lookup entire column

Generic formula 

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

Related formulas 

Lookup entire row

Sum entire column

Explanation

To lookup and retrieve an entire column, you can use a formula based on the INDEX and MATCH functions. In the example shown, the formula used to lookup all Q3 results is:

=INDEX(C5:F8,0,MATCH(I5,C4:F4,0))

How this formula works

The gist: use MATCH to identify the column index, then INDEX to retrieve the entire column by setting the row number to zero.

Working from the inside out, MATCH is used to get the column index like this:

MATCH(H5,C4:F4,0)

The lookup value "Q3" comes from H5, the array is the headers in C4:F4, 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 column number:

=INDEX(C5:F8,0,3)

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

{121250;109250;127250;145500}

Processing with other functions

Once you retrieve an entire column of data, you can feed that column into functions like SUM, MAX, MIN, AVERAGE, LARGE, etc. to retrive information of interest. For example, to get the MAX of column 3, you could use:

=MAX(INDEX(C5:F8,0,MATCH(I5,C4:F4,0)))