Getting multiple Fields from 1 row using MAX and COLLECT

Tom R

I have a sheet (IDF Check) that contains static information about my IDF's that I want to use as a kind of dashboard. I have a second sheet (IDF Checklist) that collects information from weekly checks on the IDF including Date and time checked, the inspectors name and a calculated field of the overall status of the IDF. I'm using the name field as a link between the two sheets.

For each row in IDF Check I want to find the row in IDF Checklist that has the matching name and with the latest checked date and pull the date , Checked By name, and status into the IDF Check sheet. I've been able to use the function below to get the date checked but I'm at a loss on how to get the other two fields from the same row. Any help would be greatly appreciated.

=MAX(COLLECT({IDF Checklist date checked}, {IDF Checklist Name}, Name@row))

Genevieve P.

Hi @Tom R

You're almost there! Since you now have the Max Date and the Name, we can use these two criteria in a COLLECT function again to bring back the Checked By name and the Status.

Instead of MAX, now we'll use the INDEX function.

Try this:

=INDEX(COLLECT({IDF Checklist Checked By column}, {IDF Checklist Name}, Name@row, {IDF Checklist date checked}, [Date Checked]@row), 1)

I added in the Date Checked column and used the output from your previous formula as the criteria. You'll need to update the [Date Checked] to be your column name.

{IDF Checklist date checked}, [Date Checked]@row

Then at the front of the COLLECT function you would list the column you want to bring back. In the example above it was the Checked By column, then for your Status column you'd swap this out (make sure to delete and replace the reference instead of clicking edit):

=INDEX(COLLECT({IDF Checklist Status Column}, {IDF Checklist Name}, Name@row, {IDF Checklist date checked}, [Date Checked]@row), 1)

Let me know if this makes sense and works for you!

Cheers,

Genevieve

Tom R

Hi, Genevieve. When I tried the first of the index commands, I got INVALID REF. Unfortunately I haven't had a chance to work with it and I'm off until Friday.

Genevieve P.

Hi @Tom R

How are you creating the formula? You'll need to manually select the correct cross-sheet reference ranges (versus copying/pasting from my post above). Here's information on cross sheet references.

Let me know next week if that helped!

Cheers,

Genevieve