Hi everyone!
I usually use INDEX/MATCH, but I am trying to "match" multiple criteria and found that it is better to use INDEX/COLLECT when dealing with multiple criteria. I keep returning "#INVALID VALUE" and am confused what I am missing…
I have an "Audience" sheet my team is using with employees' respective name, ID number, manager, etc. I created two columns for two different classes — in each of these columns, my idea is to bring in whether or not each individual completed these classes from a secondary sheet.
Here is a look at the Audience Sheet. The "Formated Perner" is the Employee ID.
The secondary sheet that I want to pull in the information from is the "Curriculum Item Status" shown below. I want to pull in the "Y" or "N" in the "Completed" column from this sheet to the Audience sheet based on matching the Employee ID columns and the Item IDs. Each employee is listed on this sheet twice (one for each class), so I want to bring in the Y/N from the row with the Item ID of "4684012" to the "DX_Resiliency…." column on the Audience sheet.
Currently, I have the formula on the Audience sheet set as:
=INDEX(COLLECT({Curriculum Item Status - Completion}, {Curriculum Item Status - Item ID}, "4684012", {Curriculum Item Status - Perner}, [Formated Perner]@row ), 1)
The first reference is to the "Completed" column on the Curriculum Item Status sheet; the next reference is the "Item ID" column on the Curriculum Item Status sheet and it is looking for "4684012"; and then the next reference is to the "Perner" column on the Curriculum Item Status sheet and it is looking for the same Formatted Perner on the Audience sheet. As you can see in the screenshots, the first employee on the Audience Sheet is also listed on the Curriculum Item Status sheet with "Y" for Item ID "4684012". In my brain, this should then be returning the "Y" from the Curriculum Item Status sheet to the column of the Audience sheet, but instead I am getting the #INVALID VALUE output.
Can anyone identify what I am doing incorrectly or how to better make this work?
Thank you in advance!
