Which formula can I use to pull data from one sheet to another with multiple criteria?

Dmcmillan

The above image is from one sheet. From this sheet I would like to pull the text from the Answer column into the "2.3.3" column in the below image. I tried using Index/Match and it worked for one question number but when there are multiple question numbers for the same school and SCID, I'm not able to only pull the answers for that school. For instance, in the top image there is another question number for that school with a different answer. Then below that there is the same question number for a different school with a different SCID (not shown in the image). I'm trying to use a formula to fill in the answers for each corresponding question number in the image below. I hope I explained that in a way that makes sense. Thanks in advance for any help you can provide.

Paul Newcome

You are missing the end of the formula that finishes out the INDEX function:

=INDEX(COLLECT({2023-2024 SSRA Concerns Range 1}, {2023-2024 SSRA Concerns Range 2}, [SCID (static)]@row, {2023-2024 SSRA Concerns Range 3}, "2.3.3"), 1)

Paul Newcome

That particular error indicates there is not a row that matches. What happens if you use this?

=COUNTIFS({2023-2024 SSRA Concerns Range 2}, [SCID (static)]@row, {2023-2024 SSRA Concerns Range 3}, "2.3.3")

Paul Newcome

You can use an IFERROR statement.

=IFERROR(original formula, "message if error")

AravindGP

Hi @Dmcmillan ,

You can use an Index/collect formula. Your formula would be something like this - =INDEX(COLLECT({Answer column reference}, {SCID column reference}, SCID@row, {Question number column reference}, "2.3.3") for the 2.3.3 column. Replace the last number to the question number in each column in sheet 2

Dmcmillan

Hello Aravind and thanks for you help. I tried the formula but I'm receiving the error, "#Incorrect Argument". Here's what I have:

=INDEX(COLLECT({2023-2024 SSRA Concerns Range 1}, {2023-2024 SSRA Concerns Range 2}, [SCID (static)]@row, {2023-2024 SSRA Concerns Range 3}, "2.3.3"))

Do you believe it's not working because the question number where I want the data to go is not associated with a school or SCID, but is in a column alone to collect the answer from the other sheet?

Paul Newcome

You are missing the end of the formula that finishes out the INDEX function:

=INDEX(COLLECT({2023-2024 SSRA Concerns Range 1}, {2023-2024 SSRA Concerns Range 2}, [SCID (static)]@row, {2023-2024 SSRA Concerns Range 3}, "2.3.3"), 1)

Dmcmillan

Hi Paul. I tried that based on your response to another user here. It still didn't work. I'm not sure what the issue is unless, so you mention in another post, my ranges aren't matching. I'm working on fixing that. What else do you think could be causing the error? This time the error is "#INVALID VALUE".

Paul Newcome

That particular error indicates there is not a row that matches. What happens if you use this?

=COUNTIFS({2023-2024 SSRA Concerns Range 2}, [SCID (static)]@row, {2023-2024 SSRA Concerns Range 3}, "2.3.3")

Dmcmillan

With the below formula I get a 0.

=COUNTIFS({2023-2024 SSRA Concerns Range 2}, [SCID (static)]@row, {2023-2024 SSRA Concerns Range 3}, "2.3.3")

Dmcmillan

Paul, your formula worked!!!! So, you were right, I was getting "#INVALID VALUE" because there wasn't a match. Is there something I can use to display a specific text instead of the error message?

Paul Newcome

You can use an IFERROR statement.

=IFERROR(original formula, "message if error")

Dmcmillan

Thanks Paul!

Paul Newcome

Happy to help. 👍️