How do I update Sheet 2 with data from Sheet 1 if it matches?

Sheryl Roland

In Sheet 1, users enter data via a form. In Sheet 2, I track the devices. I need the truck# from sheet 1 to populate on sheet 2 next to the name of the device. I also need the date that it was entered to populate on sheet 2 as well.

In sheet 1, ePCR Tablet, Navigator Number and CMED radio are separate columns, but in sheet 2, there is one column (Asset Name) with all devices listed.

Sheet 1

Sheet 2

I have racked my brain with =INDEX(COLLECT) and can't seem to get it right.

Thanks in advance!

Sheryl Roland

Jason Tarpinian

For an example on your Sheet 2, the formula would be:

=INDEX(COLLECT({Truck #}, {Asset Name}, [Asset Name]@row), 1)

Just replace the cross-sheet reference of {Truck #} in the formula with whatever other values you need to pull into sheet 2.

Sheryl Roland

The formula works but how do you get it to choose the latest entry? These are daily form submissions, but the formula is picking up the first entry.