Max and index match function

Simran Curry

Hi all, i have a sheet where Trailers are logged in multiple time throughout the day and what they had in them such as a PO number or a load number

in a separate sheet i want to type in the Trailer number and i want it to pull the recent entry date of the trailer and the Load number / po that came in that trailer. How can i achive that? if it can pull the time too that will be great.

Mark.poole

do you want all the information in one column.If so you need the join function. I can build that formula for you but would take a little bit. If you do separate columns and the sheet your pulling from has a created column. I would do a few helper rows. And run index match in each columns. If you provide this information I can figure the best course for you.

Simran Curry

i want the info in different columns.

also the sheet where we will be pulling the data from it will have a date column and a time column. it does not have a created column, but i can add it.

thankyou for replying

Mark.poole

@Simran Curry Ok Last question. Are the Tailor numbers ever used again?. If so you would need some sort of Unique identifier. If not a simple Index match for each column to pull the information you need.

Simran Curry

yes Trailer numbers are used multiple times . i need the very recent date and time it was logged.

Mark.poole

https://community.smartsheet.com/discussion/comment/436608#Comment_436608

Ok. Try This out and see how it works for you.

=Index(Collect({PO ref},{Trailer Number Ref},[Trailor Number Column]@row,{Date Ref},MAX({Date Ref}),{Time Ref},MAX({Time Ref})))

Simran Curry

i cant seem to get it working.

any way we can connect and you can show me?

Mark.poole

@Simran Curry

Absolutely. Do you have teams?

Also I think I see the issue with the formula I created for you. How ever I know a great work around I use all the time. I can implement for you. Adding a helper column in the sheet that you are pulling your information from. It will have an incremental number based on a few criteria.