Return Value Corresponding to Latest Date Multiple Entries

ColtonPhilp

I am looking to make an intake form to track items in a warehouse. When a person moves items I would like to set up a way to return what the last location the item was reported at. See below for format. I have used Max(Collect()) in the past, but as far as I can work out this limits me to one column for the "Unit" to live in, making the person filling out a form need to complete a form for each item, rather than being able to "Inspect" multiple items in one form.

On Sheet 1(Summary Sheet) the columns I am looking for would look like this

Item Name	Last Location
Unit A	"Formula I need help on" Needs to return "Location 3"

On Sheet 2 I would like to return the value in the "Location" Column that has the most recent date, and contains "Unit A"

Date	Location	Item 1	Item 2	Item 3	Item 4	Item 5	Item 6
1/2/2000	Location 3	Unit B	Unit A	Unit G	Unit Q	Unit W	Unit R
1/1/2000	Location 2	Unit A	Unit B	Unit C	Unit D	Unit E	Unit F

SoS | Dan Palenchar

Hello @ColtonPhilp,

I solved this in a few steps, as follows. First, in Sheet 2:

1. Add a new dropdown column with multi select enabled and add formula =JOIN([Item 1]:[Item 6], CHAR(10)) this creates a roundabout way to search across all 6 Item columns by only looking at 1 column.
2. Add a Row ID column (Auto Number). This will act as the way to identify the most recent entry as it always increases by 1.

Put it together and it looks like this:

Then, in Sheet 1:

1. Add a checkbox column called Exists and input formula =IF(COUNTIFS({SearchRange}, [Search Value]@row) > 0, 1, 0)
   1. {SearchRange} is [Item 1]:[Item 6] in Sheet 2
   2. This column is used to check if an Item Name is actually in the database. I'm doing this because some lookup formulas will return 1 as an error which will throw off the functionality.
2. Add a column Database Row ID and input formula =IF(Exists@row = 1, MAX(COLLECT({RowID}, {All}, HAS(@cell, [Search Value]@row)), 1))
   1. {RowID} is the Row ID column in Sheet 2
   2. This will pull in the largest Row ID number across all rows where the Item Name exists.
3. In your Last Location column input formula =INDEX({ReturnValue}, MATCH([Database Row ID]@row, {RowID}))
   1. {ReturnValue} is the Location column on Sheet 1.

It looks like this (though I am using more generic column names and values).

Here is an interactive dashboard you can see and use

https://app.smartsheet.com/b/publish?EQBCT=7676b26e6a124b6cb7d0d9a156825756

Hope this helps!