Returning multiple INDEX/MATCH or COLLECT on separate rows

David Dolch

I have a sheet (call it "Master Sheet") with a column called "Item ID". The "Item ID" is a unique system generated field. This sheet has an other column "Status", Status is a single select drop down column. For sake of argument lets say one of the values is "TRUE". I would like to create a separate sheet (call it "Reporting Sheet") using cross sheet references that shows ALL Items IDs where Status is TRUE on the Master Sheet. Much like you would do with a report, only I cannot use a report for certain reasons.

There has got to be a way I am missing to do this with a combination of INDEX, MATCH, and/or COLLECT functions. I know it could not be a column formula, but something that increments row by row, and I am fine with that. Something such as in row 1, perform the index match and return the first instance you find, in row 2 perform the index match and return the second instance you find, etc. The number of cases where this match would occur is limited and I am fine with IFERRORing out anything where there is no match.

Thanks

Paul Newcome

You first need a text/number column (called "Number" in this example) that has the numbers one through whatever manually entered. You will need to enter as many numbers as you think you will need, and I always suggest a little bit of a buffer.

Then you would use

=IFERROR(INDEX(COLLECT({Column To Pull Over}, {Status Column}, @cell = "status of choice"), Number@row), "")

David Dolch

As an addendum - I want each returned value in a separate row. I realize I could do it all in one cell using JOIN(COLLECT

I guess if I had to I could collect them all in one giant cell and then do some crazy parsing of the info into multiple cell, as each Item ID is of the form PSM####, so I could use LEFT or RIGHT functions to break each one out, but that is a really inelegant solution.

Paul Newcome

You first need a text/number column (called "Number" in this example) that has the numbers one through whatever manually entered. You will need to enter as many numbers as you think you will need, and I always suggest a little bit of a buffer.

Then you would use

=IFERROR(INDEX(COLLECT({Column To Pull Over}, {Status Column}, @cell = "status of choice"), Number@row), "")

David Dolch

@Paul Newcome

Thank you. The @cell for the "status of choice" was the part that was missing in my head.

Paul Newcome

Happy to help. 👍️