# Matching cell values on 2 criteria

Copper Contributor

# Matching cell values on 2 criteria

I have two separate sets of data that I'm trying to combine in to one. Sheet 1 has one row per person, and multiple columns for each of their responses to a question. Sheet 2 has multiple rows for each person's responses (i.e. 4 separate rows for each of Person 1's responses to 4 different questions). Screenshots below for an example.

What I'm trying to do is find a formula to look up Person 1's response to Question 1 on Sheet 2, and fill in that answer in the respective row and column on Sheet 1 (and so on and so forth for each question and each person). I tried using INDEX and MATCH with the formula below, but get a SPILL of too many results, when my expectation is it only should return one result, so obviously I have something wrong, or it won't work in this situation.

=INDEX(Sheet2!C1:C16,MATCH(1,Sheet1!A2=Sheet2!A1:A16)*(Sheet1!B1=Sheet2!B1:B16),0)

Any advice on a formula or how to address this is much appreciated!

Sheet 1:

Sheet 2:

2 Replies

# Re: Matching cell values on 2 criteria

``=IFERROR(INDEX(Sheet2!\$C\$1:\$C\$100,MATCH(1,(Sheet1!\$A2=Sheet2!\$A\$1:\$A\$100)*(Sheet1!B\$1=Sheet2!\$B\$1:\$B\$100),0)),"")``

You can try this formula. Enter the formula with ctrl+shift+enter if you don't work with Office 365 or Excel 2021.

# Re: Matching cell values on 2 criteria

I think the more natural formula is to use FILTER. You Microsoft 365 or newer versions to have FILTER. My formula is something like below

``=FILTER(Sheet2!C2:C9, (Sheet2!A2:A9=E2) * (Sheet2!B2:B9=F1),"")``

Sheet1

 Person Qn Answer Person 1 Question 1 2 Person 1 Question 2 3 Person 1 Question 3 4 Person 1 Question 4 5 Person 2 Question 1 9 Person 2 Question 2 1 Person 2 Question 3 5 Person 2 Question 4 6

Sheet2

 Question 1 Question 2 Question 3 Question 4 Person 1 2 3