If 2 Criteria Met On Second Sheet, Display ID Number from Second Sheet on First Sheet

Lori Flanigan

I am trying to write a cross-sheet formula in sheet 1 (field name is Row ID) to display the Row ID from a second sheet if the second sheet Newest field and ED field are both checked (see below).

I've been looking through posts and attempting INDEX/MATCH, nested IFs, etc. and nothing is working so far.

Any help would be very appreciated!

Thank you!

Lori

KPH

Hi Lori

How about something like this?

This is a mock up of your sheet 2

Then in sheet 1 you could have a column that runs from 1 to whatever you need - just counting up each row, 1, 2, 3, 4, 5, etc.

And a formula with this formula in

=IFERROR(INDEX(COLLECT({Second Sheet Row ID}, {Second Sheet Newest}, 1, {Second Sheet ED}, 1), [Primary Column]@row), "")

This uses 3 cross sheet references which I hope make sense from the names I gave them (you can use whatever name you want - just be sure to select the correct columns).

The result would be the row IDs from the sheet 2 where Newest field and ED field are both checked, like this:

What the formula is doing is checking for errors (namely if there isn't a match) and putting "" in that case (see row 5 onwards). Otherwise it is collecting the row ID where Newest is ticked, and where ED is ticked, and returning the nth time that occurs where n is the number in the primary column.

KPH

Hi Lori

Have you looked at an INDEX/COLLECT combo? The COLLECT part lets you have two criteria (whereas MATCH is just one).

Lori Flanigan

Hi,

Thanks for your question. I have read about INDEX/COLLECT, but the Newest and ED fields are on the second sheet only. The posts I'm reading don't seem to support INDEX/COLLECT for that context, but I certainly could be misinterpreting things.

Lori

KPH

Hi Lori

INDEX COLLECT will work over the two sheets, but we may need another part as it will only return the first row that matches the criteria you want a full list.

Lori Flanigan

I added the Newest field to the second sheet. That field is a column formula that flags when the most recent entry for an individual in my program is added to the sheet. So, when Newest is flagged and ED is flagged, that row is the newest entry for a person I want add to my first sheet.

KPH

Hi Lori

How about something like this?

This is a mock up of your sheet 2

Then in sheet 1 you could have a column that runs from 1 to whatever you need - just counting up each row, 1, 2, 3, 4, 5, etc.

And a formula with this formula in

=IFERROR(INDEX(COLLECT({Second Sheet Row ID}, {Second Sheet Newest}, 1, {Second Sheet ED}, 1), [Primary Column]@row), "")

This uses 3 cross sheet references which I hope make sense from the names I gave them (you can use whatever name you want - just be sure to select the correct columns).

The result would be the row IDs from the sheet 2 where Newest field and ED field are both checked, like this:

What the formula is doing is checking for errors (namely if there isn't a match) and putting "" in that case (see row 5 onwards). Otherwise it is collecting the row ID where Newest is ticked, and where ED is ticked, and returning the nth time that occurs where n is the number in the primary column.