google-sheets-formulainventory-management

How to return most recent data based on google form response timestamp

New here, but I've been wracking my brain over the last two days, and I haven't been able to figure out the solution.

I'm working on an Inventory Management tracker for personal use. I have an tab named "Index" with Columns "Item ID" and "Location", and the other tab would be the google response form with columns "Timestamp", "In/Out", "Item ID", and "Location".

I'm looking for a formula that will pull in the most recent location info from the Google response sheet, with a match to the Item ID.

I've tried filters, but keep getting a match error.

I'm open to Queries, but am very new to them, so will need some help coming up with what works best.

Solution

  • I dont know about google-sheets, but in excel you could add a unique key column to your Response table - eg =ItemID&"-"&TimeStamp

    Then in your Items table find the most recent Timestamp for that Item with MAXIFS

    Then use that most recent timestamp value for the item to to a vlookup using the Response.Key column to get the location