< Go Back

Lookup entire column

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.

Syntax

The general formula used to lookup and retrieve values from a column is:

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

Example

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:
=INDEX(C5:F8,0,MATCH(K5,C4:F4,0))

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

Explanation

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

MATCH(K5,C4:F4,0)

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

=INDEX(C5:F8,0,4)

Setting row number to zero causes the INDEX function to retrieve all the four numbers in the column as the final result:

{231750;146860;115500;170650}

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.

 

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California
This blog is one way to figure it out. A 20 minute chat session with an expert is a better way.Get step-by-step guidance on your question

Leave a Comment

avatar