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

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.

• ✭✭✭✭✭✭

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)

• ✭✭✭✭✭✭

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")

• ✭✭✭✭✭✭

You can use an IFERROR statement.

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

• ✭✭✭✭✭

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

Thanks,

Aravind

Reach out for any help on licenses, configuration, or training

• 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?

• ✭✭✭✭✭✭

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)

• 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".

• ✭✭✭✭✭✭

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")

• 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")

• 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?

• ✭✭✭✭✭✭

You can use an IFERROR statement.

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

• Thanks Paul!

• ✭✭✭✭✭✭

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!