Are you tired of being limited by the Lookup function in Google Sheets? Do you wish you could return multiple column outputs? Well, you’re in luck! In this article, I’m going to share with you four alternative formulas that will allow you to achieve last row lookup and array results in Google Sheets. Say goodbye to limitations and hello to endless possibilities!
Understanding Last Row Lookup
Before we dive into the formulas, let’s first clarify what we mean by “last row lookup.” Essentially, it refers to retrieving the value from the last row in a specific range. To illustrate this concept, let’s consider an example with two tables and two Lookup formulas. In both formulas, we’ll be searching for the name “James.”
In the first formula, the search key to lookup is located in cell D1, while the search range is A2:A9, and the result range is B2:B9. Similarly, in the second formula, the search key is in cell D12, the search range is A13:A18, and the result range is B13:B18.
The Lookup formulas scan through the search range (column A) for the key “James” and return the corresponding value from the result range (column B). It’s important to note that the data must be sorted for this to work correctly. However, in the second table, where there are duplicates, the Lookup in cell E12 returns the value from the last row in the result range. Hence, the term “last row lookup.”
Formula Options for Last Row Lookup and Array Result
The Lookup function in Google Sheets only allows for a single column (or row) output in the result range. But fear not! We have four powerful formulas to help you achieve last row lookup and array results. Let’s explore each one:
Formula 1: Index and Filter
The first formula utilizes the combination of the Index and Filter functions. It’s perfect for those who prefer a straightforward approach.
In this formula, we’ll be using the same sample data and criterion. We want to find the last ordered and received quantities of the fruit named “mango,” which are located in row #7.
The formula is as follows:
=INDEX(FILTER(B2:C,A2:A=E2),COUNTIF(A2:A,E2))To break it down:
- The Filter formula filters the rows containing the fruit name “mango”:
FILTER(B2:C,A2:A=E2)
- Only columns B and C are included in the filter range, so all the ordered and received quantities of the fruit “mango” will be filtered.
- These quantities are from rows 6 and 7. To extract the last row, we use the Index function:
COUNTIF(A2:A,E2)returns 2 (the last row)INDEX(reference, [row], [column])takes the filter formula as the reference and specifies the row we want to return.
This formula allows for last row lookup and array result in Google Sheets.
Formula 2: Vlookup and Filter – Recommended
If you’re not a fan of the Index function, the second formula utilizing Vlookup and Filter might be more appealing to you.
Here’s how the formula looks:
=ArrayFormula(VLOOKUP(E2,FILTER(A2:C,A2:A=E2),{2,3},1))- The search key is “mango” (cell E2).
- The filter formula remains the same, but with a slight change in the filter range (A2:C instead of B2:C).
- We want to retrieve columns 2 (Ordered) and 3 (Received) in the output.
This formula is highly recommended for last row lookup and array result in Google Sheets.
Formula 3: Sortn and Filter
The third formula combines the Sortn and Filter functions. Although it might not be the most recommended option, it offers valuable insights into the usefulness of Sortn.
Here’s the formula:
=SORTN(FILTER(B2:C,A2:A=E2),1,0,SEQUENCE(COUNTIF(A2:A,E2),1),0)Here’s how it works:
- The filter formula remains the same as in Formula 1.
- Sortn sorts the filtered data in descending order and returns the first row.
- To achieve the descending order, I’ve employed sequential numbers generated by the Sequence function.
- This formula provides an alternative approach to last row lookup and array result in Google Sheets.
Formula 4: Query Formula
Lastly, we have the Query formula. This formula filters the row containing the criterion “mango” and then offsets the rows accordingly.
Here’s the formula:
=QUERY(A2:C,"SELECT B,C WHERE A='"&E2&"' OFFSET "&COUNTIF(A2:A,E2)-1)- The formula filters the row based on the criterion “mango.”
- It then offsets 2-1 (Countif – 1) rows.
With this formula, you can achieve last row lookup and array result in Google Sheets.
Final Thoughts
These four formulas unlock a variety of options for last row lookup and array result in Google Sheets. By utilizing Index and Filter, Vlookup and Filter, Sortn and Filter, or the Query formula, you can break free from the limitations of the Lookup function and explore new possibilities for data manipulation.
Master these formulas and take your Google Sheets expertise to the next level. Remember, if you’re interested in learning more tips and tricks for Google Sheets, be sure to check out Crawlan.com for valuable insights and resources.
Happy Sheets exploring!
