Return the second match for a VLOOKUP

Ask for Help
1

I am building an app where the same item can be sold multiple times, retaining it’s itemID. This is important because we want to be able to get a history of that item.

I have the following
=ARRAYFORMULA(IF(LEN($A3:$A) = 0, “first”, IF(ISERROR(VLOOKUP($A3:$A, Sales!$A3:$A, 1, false)), “”, VLOOKUP($A3:$A, Sales!$A3:$C, 3, false)))) to assign a new owner. All it’s doing is looking at a table like this

itemID From To Price
39 prototype@email.com laura@email.com -10.00
6 prototype@email.com sandro@emai.com -10.00
11 prototype@email.com laura@email.com -10.00
71 prototype@email.com laura@email.com -10.00
39 laura@email.com sandro@emai.com -25.00
32 prototype@email.com sandro@emai.com -10.00

The problem is that the formula trying to determine the current owner of the items but is only returning itemID 39 in its first instance.
Item 39 has first been sold by the app to laura and then sold by laura to sandro.
Yet the formula is only returning the email of the first transaction as the current owner.

I saw that with VLOOKUP you can turn to false and sometimes it returns the last entry but it doesn’t seem to work for mine.

Anyone knows how I can solve this?

2

Add a time stamp to the table, sort in reverse order, lookup last sold item which now is first in you table.

2 Likes
3

Hi Ralf,
Thank you for your answer. Is there away that the time stamp is added automatically?
The sales table is filled from a form in the glide app. Not sure how can I pass the time stamp from the app.

4

You can find the time stamp in the compnent list!

5

Hi Ralf, thank you so much.

6

Take a look at concepts.glideapp.io

Take a look at the sheet and copy the app to look at the Photo Choice example. The formula I use to set the users image looks for the last item on a list of form submissions. It’s a vlookup, but slightly different. The Currency Transfer/Purchase demo should give you ideas too.