Forum Discussion
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
Hi Robobobob
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 https://support.microsoft.com/en-au/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
=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.
