Lookup Previous Values Dynamically in Excel and Google Sheets

Published on

Sometimes, you may want to look up the previous values relative to the current row. For example, in a vehicle list, you can identify the last filled gasoline quantity and compare it with the current quantity to understand how much gasoline was filled during the previous entry. The same formula works seamlessly in both Excel and Google Sheets.

We will use a custom LAMBDA function that incorporates XLOOKUP within the MAP helper function for this task.

Formula to Lookup Previous Values Dynamically in Excel and Google Sheets

Here’s the formula:

=MAP(A1:A10, B1:B10, LAMBDA(Λ,Σ, XLOOKUP(OFFSET(Λ, 1,), A1:Λ, B1:Σ,"",,-1)))
  • A1:A10 is the lookup range (e.g., vehicle registration numbers).
  • B1:B10 is the result range (e.g., gasoline quantities).
  • A1 and B1 are the starting rows of your table.

This formula dynamically looks up the current row value within the range up to the previous row and returns the last matching value.

Example: Lookup Previous Values Dynamically

Sample Data:

Lookup previous values dynamically in Excel or Google Sheets

The table contains:

  1. Vehicle Registration Numbers in column A.
  2. Gasoline Quantity (gallons) in column B.

The range is A1:B, where A1 and B1 are headers.

To retrieve the last gasoline quantity for each vehicle, enter the following formula in cell C2:

=MAP(A1:A10, B1:B10, LAMBDA(Λ,Σ, XLOOKUP(OFFSET(Λ, 1,), A1:Λ, B1:Σ,"",,-1)))

This formula looks up previous values seamlessly in both Excel and Google Sheets.

Formula Breakdown

Before diving deeper, here’s an alternative drag-down formula that achieves the same result:

Enter this XLOOKUP and OFFSET combination formula in cell C2 and drag it down:

=XLOOKUP(OFFSET(A1, 1,), $A$1:A1, $B$1:B1,"",,-1)

Explanation:

  1. OFFSET(A1, 1, ):
    • This is the lookup value (or search key).
    • In cell C2, it fetches the value from A2 as the search key.
  2. $A$1:A1:
    • This is the lookup range, which expands as you drag the formula down.
  3. $B$1:B1:
    • This is the result range from which the formula retrieves the corresponding value.
  4. Search Mode -1:
    • Ensures the formula searches bottom-to-top in the range for the most recent match.

How It Works:

  • In C2, the formula looks up the current row value in A1:A1 and returns the corresponding value from B1:B1.
  • When dragged to C3, the formula expands to A1:A2 and B1:B2, effectively searching the prior entries for the most recent match.

This logic is the foundation for looking up previous values in both Excel and Google Sheets.

Automating with a Custom LAMBDA Function

To avoid manual drag-down formulas, we can automate the process using a LAMBDA function:

LAMBDA(Λ,Σ, XLOOKUP(OFFSET(Λ, 1,), A1:Λ, B1:Σ,"",,-1))

Explanation of the LAMBDA Function:

  • Λ: Represents the current element in the first array (A1:A10).
  • Σ: Represents the current element in the second array (B1:B10).

Using the MAP function, this LAMBDA is iterated over both arrays to dynamically compute the previous values for all rows.

Syntax: MAP(array1, [array2, …], lambda)

Key Points:

  1. MAP Function: Iterates the LAMBDA function over two arrays.
  2. Greek Letters (Λ and Σ): Used to avoid conflicts with cell references.
  3. Dynamic Range Expansion: Automatically adjusts lookup ranges for each row.

By combining XLOOKUP with MAP and LAMBDA, you can dynamically lookup previous values in both Excel and Google Sheets without manual intervention.

Additional Resources

Prashanth KV
Prashanth KV
Your Trusted Google Sheets and Excel Expert Prashanth KV is a Diamond Product Expert in Google Sheets, officially recognized by Google for his contributions to the Docs Editors Help Community and featured in the Google Product Experts Directory. With years of experience working with Google Sheets and Excel for multinational firms in Mumbai and Dubai, he has been blogging since 2012, offering practical, real-world spreadsheet solutions that professionals rely on. Explore his blog to learn advanced formulas, automation tips, and problem-solving techniques to elevate your spreadsheet skills.

Mode of Comma-Separated Numbers in Excel (Dynamic Array)

There is no dedicated function in Excel to directly find the mode of comma-separated...

How to Find Mode of Comma-Separated Numbers in Google Sheets

Working with comma-separated numbers inside a single cell is a common scenario in Google...

How to Count Merged Cells in Google Sheets (and Get Their Size)

Sometimes, you may have blocks of merged cells in a column or row. But...

Count Consecutive Workday Absences in Google Sheets

This tutorial offers a powerful formula-based solution to count consecutive workday absences in Google...

More like this

Reset SCAN by Another Column in Google Sheets and Excel

Resetting SCAN function results based on values in another column is a topic of...

How to Calculate Maximum Drawdown in Excel and Google Sheets

You can use the following dynamic array formula to calculate maximum drawdown (MDD) in...

Sort Data but Keep Blank Rows in Excel and Google Sheets

Using a dynamic array formula or the FILTER command, we can sort data and...

LEAVE A REPLY

Please enter your comment!
Please enter your name here

This site uses Akismet to reduce spam. Learn how your comment data is processed.