In this post, let’s learn how to get the LOOKUP result from a dynamic column in Google sheets.

One of the purposes of the function LOOKUP is, in a table, to search down a column for the last occurrence of a given key.

Then get the result from a specified column or the last column from that table.

Here is one example:

Table # 1 (Data Range A2:D9)

I want to search down the last occurrence of the date 1-Jan-2021 in column range A2:A9 and return the result from a known column range, i.e., C2:C9 (“Acrylamide Liquid”).

For this, I can use the following LOOKUP formula in Google Sheets.

Formula # 1:

=lookup(date(2021,1,1),A2:A9,C2:C9)

Result: 349.25

Note:- I have specified the date in DATE(year, month, day) format as per the DATE function.

To get the result from the last column range (D2:D9), we can use the following LOOKUP.

Formula # 2:

=lookup(date(2021,1,1),A2:D9)

Result: 122.60

In the first formula, I have separately specified the search range (A2:A9) and result range (C2:C9). Whereas in the second formula, I have used the entire data set.

The formula # 1 syntax is LOOKUP(search_key, search_range, result_range) and the formula # 2 syntax is LOOKUP(search_key, search_result_array).

To get the LOOKUP result from a dynamic column in Google sheets, we will use the second syntax.

I’ll come to that. Before that, you should know about the dynamic column in the LOOKUP.

What Does It Mean by Lookup Result from a Dynamic Column

Dynamic column:- The LOOKUP will return the result from a column based on another search across the column header.

For example, let’s consider the closing stock of some products (table # 1 above).

I want to search down column A for the last occurrence of the date 1-Jan-2021 in column A and return value from columns B (“Acrylic Acid”), C (“Acrylamide Liquid”), or D (“Acrylamide Powder”).

In this, I want to specify the columns B, C, or D, dynamically.

I have tried to illustrate the same below (image # 1).

When I change the value in cell F4 to “Acrylamide Liquid”, the result in cell G4 should be 349.25 (the value from cell C5).

That’s what I meant by getting the LOOKUP result from a dynamic column.

Further, if I change the date in cell G1 to 2-Jan-2021, I should get 300.85, i.e., the closing stock of the item “Acrylamide Liquid” on that date.

Two Formulas to Get LOOKUP Result from a Dynamic Column in Google sheets

To get the LOOKUP result from a dynamic column as above, we can use QUERY or FILTER within the LOOKUP in Google Sheets.

As I have mentioned in the beginning, we will follow the syntax LOOKUP(search_key, search_result_array) for this purpose.

In the LOOKUP, we can use an expression to control the search result array. I mean, make the search result array dynamic.

Dynamic Search Result Array in LOOKUP

The key to lookup the last value from a dynamic column in Google Sheets is all about using a FILTER or QUERY formula (expression) in the Lookup search result array.

The logic is like this –

Instead of specifying A2:D9, the whole data set as the search_resut_array, we should filter the required columns to specify.

I mean, we need to use A2:A9 (the date column) and filter another column based on the item in F4 (column label). That will be the search_result_array to use.

The below formulas do that.

If we use Filter;

={$A$2:$A,filter($B$2:$D,$B$2:$D$2=$F4)}

If we use Query;

=query({$A$2:$D},"Select Col1,Col"&match(F4,$A$2:$D$2,0))

Both the formulas will return the same output as below. So you can pick either of the ones to use as the search_result_array in the LOOKUP.

In this formula, the date column is static, and the item column (closing stock) is dynamic.

If we change the F4 value to “Acrylamide Powder”, the formula will return the values from that column instead of the “Acrylic Acid column”.

The date column will remain the same.

The above are the two main formulas to use to get LOOKUP results from a dynamic column in Google sheets.

Let me show you how to use them to write the required formulas.

Formula to Get LOOKUP Result from Dynamic Column

Now let’s code the LOOKUP using Filter first.

Formula to use in cell G4:

=lookup($G$1,{$A$2:$A,filter($B$2:$D,$B$2:$D$2=$F4)})

Here is the alternative.

=lookup($G$1,query({$A$2:$D},"Select Col1,Col"&match(F4,$A$2:$D$2,0)))

In the above formulas, we are controlling the dynamic column from the value in cell F4. Here is a slightly different approach that will be more practical in real-life use.

Finding Closing Stock of All Items

In the above example, instead of changing the F4 value, we can use them as below in the range F4:F6 and then drag the G4 formula to G6.

It will enable us to see the closing stock of all the items on 1-Jan-2021.

When we want the closing stocks of all the items on a different date, do as follows.

Change the date in cell G1 from 1-Jan-2021 to 2-Jan-2021 to get the closing stock of that particular date.

This way we can get LOOKUP result from a dynamic column in Google sheets.

But I purposefully didn’t mention one important point that the above formula is for a sorted data set.

The dates in column A must be in chronological order for the formula to return the correct result. Because LOOKUP is for a sorted range.

Can I modify and use the above two formulas in an unsorted data set?

Yes! We need to modify the Query or Filter used as the LOOKUP search_result_array slightly. Find the tips below.

Unsorted Data in Search Result Array

Please take a look at the range G4:H11 on image # 2 above.

You can see that the search result array formula in cell G4 returns all the rows from the data set.

We want the rows that match the date in cell G1. So that, we can avoid the unsorted issue.

We can use the date criteria within the Query to filter out the rows that do not match the date in cell G1.

=QUERY({$A$2:$D},"Select Col1,Col"&match(F4,$A$2:$D$2,0)&" where Col1=date '"&TEXT(&G&1,"yyyy-mm-dd")&"'")

The above will be the search_result_array in the LOOKUP. If you prefer to use FILTER, then use the below one.

=filter({$A$2:$A,filter($B$2:$D,$B$2:$D$2=$F4)},$A$2:$A=$G$1)

That means we can use either of the below two formulas to get the LOOKUP result from a dynamic column in a sorted or unsorted data set (table) in Google sheets.

Formula # A

=lookup($G$1,QUERY({$A$2:$D},"Select Col1,Col"&match(F4,$A$2:$D$2,0)&" where Col1=date '"&TEXT($G$1,"yyyy-mm-dd")&"'"))

Formula # B

=lookup($G$1,filter({$A$2:$A,filter($B$2:$D,$B$2:$D$2=$F4)},$A$2:$A=$G$1))

To test, please do as follows.

1. Select A3:D9.

2. Go to the DATA menu and click “Randomize range”.

That’s all. Thanks for the stay, Enjoy.

Sample_Sheet_23121

Resources:

• Lookup Last Partial Occurrence in a List in Google Sheets.
• Lookup to Find the Last Occurrence of Multiple Criteria in Google Sheets.
• Two-way Filter in Google Sheets [Dynamic Vertical and Horizontal Filter].
• How to Find the Last Matching Value in Google Sheets.