My product/use case: I seek to build a camera inventory. Each camera has a unique identifier. Cameras are mobilized to different locations on different days. I need help making a VLOOKUP or equivalent combination of formulae functions to run 2 queries cross-referencing 2 grids, searching a column with multiple dropdown values, and querying for most recent dates. Any guidance is appreciated.
Part 1: Ledger grid. Each day, when end users drop off a set of cameras, they fill out a Form, select the camera IDs from a dropdown list, and enter the dropoff location. These Form entries fill up the Ledger.
Part 2: The Live grid has a row for each camera. I need the Live grid to display the most recent dropoff location for each camera.
- search the "Camera ID" column for the unique ID
- search the "Created Date" column for the most recent value
- display the corresponding value "Location" column
Here are the formulae I tried so far. Couldn't get the function to work, not in the ledger grid (where the Form entries are added), and not in the live grid, which needs to actually display the locations.
Sheet Summary (in LEDGER grid)
=IFERROR(JOIN(COLLECT({Created Date}, {Camera ID}, HAS(@cell , [Camera ID]@row )), CHAR(10)), "")
Lookup Formula (in LIVE grid)
=IF(CONTAINS([Camera ID]@row , {Camera Inventory Ledger: Camera ID Ref}), VLOOKUP([Camera ID]@row , {Camera Inventory Ledger: Camera ID + Location}, 2, false), "No Match")
I see that when the cell DOES find a value in the Ledger column, it DOES NOT work (error message #NO MATCH). When the cell DOES NOT find a value in the Ledger column, the IF function DOES work (it displays the provided text "No Match")
