I have a table of items that tracks the price over time. When I add a new row to the table, and the item name already exists within the table, I would like to retrieve the most recent “new price”, and store it in the “prev price” column.
| Item | Date | Prev Price | New Price |
|---|---|---|---|
| apple | 2023/01/01 | 1 | 2 |
| banana | 2023/01/01 | 1 | 2 |
| apple | 2023/02/01 | 2 | 3 |
| banana | 2023/02/01 | 2 | 3 |
| apple | 2023/03/01 | 3 | 4 |
| banana | 2023/03/01 | 3 | 4 |
| apple | 2023/04/01 | (should pull 4) | 5 |
In the above example, when “apple” is entered into A8, it should look back and find “apple” at A6, then populate C8 with the value from D6.
You can use XLOOKUP with search_mode=-1
=XLOOKUP(A8,A$2:A7,D$2:D7,,,-1)