How to retrieve and map survey responses from a vertical table to a horizontal

[sp_key]

Hi all,

I have executed a Customer Survey and now need to do the analysis of their responses.

I have one table with the questions and the responses as provided by the third party platform I used to conduct the survey. I don't want to change the format of that table as I want to make sure what I build is repeatable.

I now need to map (on a second table) the responses I received people's names.
I have already done significant work using INDEX/MATCH to go through a third table (not mentioned above) in order to retrieve people's names, professions and other characteristics.

Where I'm getting stuck is the mapping from Table A (vertical) to Table B (horizontal).
I have uploaded a screenshot to illustrate the problem.

Can you please help me retrieve the data I need for columns J-M?

Many thanks in advance
Spyros

 

[GraH]

One way is potentially this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	Q	R	Id	FN	LN			ID	FN	LN	Q1	Q2	Q3	Q4
3	Q1	Yes	555534	Barry	Smith			555534	Barry	Smith	Yes	Yes	Yes	No
4	Q2	Yes	555534	Barry	Smith			345677	Mike	Smith	No
5	Q1	No	345677	Mike	Smith			123456	John	Smith	Yes
6	Q3	Yes	555534	Barry	Smith
7	Q1	Yes	123456	John	Smith
8	Q4	No	555534	Barry	Smith
9
Sheet1

Cell Formulas
Range	Formula
K3:N5	K3	=IFERROR(INDEX($B$3:$B$8,AGGREGATE(15,6,(ROW($A$3:$A$8)-2)/(($A$3:$A$8=K$2)*($C$3:$C$8=$H3)),1)),"")

 

[sp_key]

One way is potentially this:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1
2	Q	R	Id	FN	LN			ID	FN	LN	Q1	Q2	Q3	Q4
3	Q1	Yes	555534	Barry	Smith			555534	Barry	Smith	Yes	Yes	Yes	No
4	Q2	Yes	555534	Barry	Smith			345677	Mike	Smith	No
5	Q1	No	345677	Mike	Smith			123456	John	Smith	Yes
6	Q3	Yes	555534	Barry	Smith
7	Q1	Yes	123456	John	Smith
8	Q4	No	555534	Barry	Smith
9
Sheet1

Cell Formulas
Range	Formula
K3:N5	K3	=IFERROR(INDEX($B$3:$B$8,AGGREGATE(15,6,(ROW($A$3:$A$8)-2)/(($A$3:$A$8=K$2)*($C$3:$C$8=$H3)),1)),"")

Hey, I can't thank you enough for your help
Just to make sure I understand what I'm doing, can you please help me by clarifying what is the meaning of the '15,6' next to AGGREGATE and the '-2' after ROW?

Cheers
Spyros

 

[GraH]

Hi Spyros,

15 stands for the SMALL function and it will return the n-th smallest which is the k-value in the function argument. 6 allows to ignore errors caused in the array calculation where I divide row numbers by a logical check like 'equal to something'. Thus row numbers 1,2,...n get divide by 1, 0, 0, 1 and results in a array filled with numbers and errors.
Why ROW()-2? Because your range start in row 3, but index requires a correct position in the range A3:A8. So row(A3) should return 1, hence the minus 2.

I hope that's clear enough.

 

[sp_key]

Thanks! Makes sense and the formula worked fine