Using INDEX(COLLECT()) or VLOOKUP

ChrisUPC

I have a data base that has part numbers listed multiple times based on production date. I am trying to gather the status (RED, GREEN, YELLOW) symbol from another column but I want to make sure it is from the most recent addition to the data base. New data is added via form to the top of the DB.

I believe VLOOKUP will return the value at the top of the sheet but I really like using INDEX(COLLECT()) to locate the info incase the column order ever changes but I don't know how to ensure it is the most recent data since the criterion (Part #) is listed multiple times.

Andrée Starå

Hi @ChrisUPC

I hope you're well and safe!

Do you already have the autonumbering column or/and the Created Date column included in the sheet?

Be safe, and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

ChrisUPC

Yes. The "Created Date" Column is present.

Leibel S

@ChrisUPC

Add column called 'Max' to your sheet:

=IF(MAX(COLLECT([Created Date]:[Created Date], [Part #]:[Part #], [Part #]@row)) = [Created Date]@row, 1, 0)

Then to get the latest status use the below:

=INDEX(COLLECT({reference to symbol},{reference to MAX column},@cell=1),1)

Change references and/or column names to match yours.