To return an entire column you need array returning functions like INDEX or OFFSET. Both of these functions can return arrays, as well as single values, which can be used in other functions like SUM, AVERAGE or even another INDEX or OFFSET. How to return an entire column article will explain you how it can be done.
Syntax
=INDEX(data without headers, 0, MATCH(search value, headers of data, 0))
=OFFSET(anchor point before headers, 1, MATCH(search value, headers of data, 0), ROWS(any column of data or even data itself), 1)
Steps
- Start with =INDEX( which returns the range
- Type or select the range includes data C3:E7,
- Continue with 0, to specify that you want entire column
- Use MATCH( to find location of desired column
- Select the range which includes the value that specifies the column H3,
- Select the range which includes the value that includes the headers C2:E2,
- Type 0)) to select the exact match method and close both INDEX and MATCH formulas
How
As it is mentioned above, to return an entire column or row, you need to use array returning functions. An alternative way may be to use array functions. However, in most occasions they are fairly complex, unnecessary (due to existing formulas) and run slower.
The INDEX and OFFSET are powerful functions to return a value or range when they are combined with MATCH function which can handle the lookup process. Let's see how they work:
INDEX
The trick is to use "0" as row argument which specifies that you want to entire column in data instead of a single cell. The rest is how it is done with regular INDEX-MATCH. So use the MATCH function to find the location of a column:
MATCH(H3,C2:E2,0)
The INDEX function will return an array which includes values in that column.
The result: {71;64;56;80;80}
What Excel displays: 71
Please note that Excel will show only the first value of the array if you do not them in another formula. We used the AVERAGE function to demonstrate how the array can be used.
=AVERAGE(INDEX(C3:E7,0,MATCH(H3,C2:E2,0)))
The result: 70.2
OFFSET
The OFFSET function uses a similar approach as well. Instead of INDEX that you need to select whole data range, the OFFSET function requires the cell outside of the data range which can be used as an anchor point.
Another difference is the OFFSET returns an array when its optional arguments are used. These arguments basically represents the size of rows and columns to return.
Briefly; you need to specify where the column starts and how long it is. The MATCH function works the same as before to lookup the column we need and the ROWS function returns the column length. Of course we used "1" as row number because we only need a single column.
=AVERAGE(OFFSET(B2,1,MATCH(H3,C2:E2,0),ROWS(B3:B7),1))
