How Can I Make Cells Populate with Up-to-Date Information and still Work with My Existing Formula?

Question

Here is my sheet, feel free to make edits to it: https://docs.google.com/spreadsheets/d/1jeFlhBlcCASe4MLVS3dGcefYe_c7HioqblNKi8GKlnM/edit?usp=sharing

I have created a checkout process where a barcode on a book is scanned in using a Google form. The responses populate in a sheet where I then have a Master Status List of each item that shows whether it is checked IN or checked OUT. Using =INDEX($D$2:$D$123,MATCH(2,1/(C2:C123=F4))) formula in the Master Status List allows me to see an up to date status because the formula will display the most recent instance of a value in Column C and pair it with the corresponding value in Column D.

Here's where I am stuck: I would also like the information in Column A and B to show in the Master Status List, so that when someone is referencing it they can tell right away who has the item or who turned it in, and when.

It's a little bit more difficult to think of a formula because Column C Items will never change, so the formula that I'm using works, but Column A and B will have different times and different names frequently, so it would be hard to have a formula look up the most recent instance of a value and accurately pair it with the values in Column C and D.

For instance, look at the information in Row 23. Then look at F23+G23. I have the Item and Status but if I'm looking at the table, I have no idea who checked it out and when, so I need the information from A23+B23 to populate too. Which would be easy, if it was just that one instance. However, let's say someone else, J. Doe, checks in the item (BOB Books set 2) on 12/22. That information is going to populate in row 34 from the Google form response, and my Item and Status cell in the Master List will update easily and accurately. But the Name and Timestamp in the Master List will not because the sheet has no way of knowing how to pull that corresponding up-to-date info.

Any troubleshooting or ideas would be appreciated. Thanks!

Answer 1

You can try with this formula in column G:

=xlookup(F4,C2:C,{D2:D,B2:B,A2:A},,0,-1)

Or, for the whole column:

=INDEX(IFERROR(SPLIT(BYROW(F4:F,LAMBDA(each,JOIN("|",xlookup(each,C2:C,{D2:D,B2:B,A2:A},,0,-1)))),"|")))

Answer 2

Both input and output reference dynamic.

=REDUCE({"Status","Stuff","Time Stamp"},F4:INDEX(F4:F,COUNTA(F4:F)),LAMBDA(a,x,{a;
QUERY(A2:D,"select D,B,A where C='" & x & "' order by A DESC limit 1",0)}))

See your file, sheet harun24hr.

I have used UNIQUE() function in F4 cell, if there any new items scanned then it will populate in F column automatically.