Index Collect returning invalid value

Melissa Torrez

Hello, I am using an Index collect formula to collect data from another sheet based on a unique ID entered in a sheet. Ultimately, I am trying to copy data form rows in another sheet however I don't want to use the copy row function because I only need specific fields Therefore I have created a new sheet that if the ID is typed into the ID field each column will use the index collect formula to collect the data needed based on the row ID.

Here is my formula for the current status field:

=INDEX(COLLECT({Current status}, {Row ID}, ID@row), 1)

I want to populate this sheet:

Here is the source sheet where I am retrieving status:

Paul Newcome

Do you have that error in either the {Current status} or the {Row ID} range?

I don't see the ID listed in the source sheet. Have you checked that there is in fact a match?

Melissa Torrez

@Paul Newcome It is in the current status range. Source sheet has an auto generated Row ID far left column and in the formula sheet I am just calling it ID, but I am typing a number in from the source sheet that matches the row ID I want to use to copy specific fields over

Paul Newcome

You will need to get rid of the error in the source data.

Cheryl Hishmeh

I have a question similar. I have a working Countif with a formula =COUNTIF({Offender Attendance OR Lookup}, =[Appointment Lookup]@row) - returned answer 3.

I want to do the similar formula and get all the ids for each appointments - but it returns a " " - =IFERROR(INDEX(COLLECT({Offender Attendance Participant ID}, {Offender Attendance OR Lookup}, =[Appointment Lookup]@row), ", "), "")

Paul Newcome

@Cheryl Hishmeh Replace the INDEX function with a JOIN function.

Cheryl Hishmeh

Thank you that works!