Why is INDEX MATCH returning more than 1 value when used in a query while the same statement outside-CodePudding

Example file: https://docs.google.com/spreadsheets/d/1M-o8Mu3vBrBgs1wC1WL5kqK61m6Vu1K5ylGByKDCZTo/edit?usp=sharing

On the sheet "To be coached" I have a query in cell A2. The query might not be optimal and maybe it's convoluted, but it works, aside from one small detail that I just can't get to work. This is the current query.

=QUERY({
IFERROR(QUERY(FILTER(ARRAYFORMULA({MATCH('Quiz results'!F9:F,Team!G2:G,0),'Quiz results'!A9:A,'Quiz results'!C9:C,'Quiz results'!D9:D,IFERROR('Quiz results'!A9:A/0),"Question:"&CHAR(10)&'Quiz results'!G8&CHAR(10)&CHAR(10)&"Provided answer:"&CHAR(10)&'Quiz results'!G9:G&CHAR(10)&CHAR(10)&"Correct answer:"&CHAR(10)&'Quiz results'!G7}),'Quiz results'!G9:G<>'Quiz results'!G$7),"SELECT Col1,Col2,Col3,Col4,'Quiz',Col5,'"&'Quiz results'!B6&"','"&'Quiz results'!G6&"',Col6 WHERE Col2 IS NOT NULL LABEL 'Quiz' '','"&'Quiz results'!B6&"' '','"&'Quiz results'!G6&"' ''"),{"","","","","","","",""})
},"SELECT * WHERE Col1 IS NOT NULL ORDER BY Col2,Col1")

Column B shows names, which for the sake of the example I have obfuscated into "Person 1" and so on. The result I'm trying to achieve is that column A shows whether the person mentioned in column B has an active status or not. That information is present on the sheet "Team" in column A.

So far, I've been able to get the query to present the row number of an array based on information from the sheet "Team" which contains the status of the relevant person. To do this I used a MATCH statement.

MATCH('Quiz results'!F9:F,Team!G2:G,0)

The problem occurs when I try to get the actual "Yes" and "No" values from the "Team" sheet by combining the MATCH statement with an INDEX statement.

INDEX(Team!A2:A,MATCH('Quiz results'!F9:F,Team!G2:G,0),1)

When I do this, the entire query fails with the message "Query completed with an empty output". This is probably due to the IFERROR statement. When I remove that, the error is as follows.

Function ARRAY_ROW parameter 2 has mismatched row size. Expected: 1. Actual: 998.

From what I can gather, this means that the INDEX MATCH statement is providing 998 rows while the expected input for the query is only 1 row. But I don't understand why it is returning so many rows. When I use the same INDEX MATCH statement on the sheet "Test" in cell A2, it returns only one cell as a result, giving "Yes". (That should actually give a "No" for the particular person on that row, but that is a different problem which might just be occurring due to using the statement in a different context.) Even when I encapsulate the formula in an ARRAYFORMULA, I still only get 1 cell as a result. So why is the same formula giving 998 rows when it's used in the query?

I am open to any and all ideas on how to get column A on sheet "To be coached" to show the correct "Yes" and "No" values for each person based on their status in sheet "Team" by using a single formula in cell A2. The reason for this is that in the end I want to use those values to filter the query result so that it doesn't show data for inactive people. (...WHERE Col1<>"No"...)

CodePudding user response：

try removing that 1 in INDEX which will solve your ARRAY_ROW error:

=INDEX(Team!A2:A, MATCH('Quiz results'!F9:F, Team!G2:G, 0))

fyi, there is VLOOKUP:

=INDEX(IFNA(VLOOKUP('Quiz results'!F9:F, {Team!G2:G, Team!A2:A}, 2, 0)))