This post will guide you on how to lookup and retrieve an entire column. The
lookup formula can be used to return or retrieve a value from a range (one column) or from an array.
The general formula used to lookup and retrieve values from a column is:
The formula is created using a combination of MATCH and INDEX functions. For instance, in the screenshot above, the expression adopted to retrieve all Y4 values is shown below:
In the screenshot, the formula is enclosed in a SUM function just to show that all the values are accurately returned.
Figure 1.Looking up and retrieving an entire column
The MATCH function is used for indicating the column index and the INDEX function is used to retrieve or return all the values in the column by using zero as the row number. From the example, we use MATCH to retrieve the column index as shown
The INDEX function contains the array of values which ranges from cell C5 to F8 (C5:F8), and the specified column number is 4, as given by MATCH. To retrieve the entire values in the column,we set the row number to zero
Setting row number to zero causes the INDEX function to retrieve all the four numbers in the column as the final result:
When the entire formula is embedded in the SUM function, it correctly returns the sum of the entire column which is 664760. This shows that each value in the column are correctly returned.