If 2 Criteria Met On Second Sheet, Display ID Number from Second Sheet on First Sheet
I am trying to write a crosssheet 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
Best Answer

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.
Answers

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

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

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.

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.

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.
Help Article Resources
Categories
Check out the Formula Handbook template!