Index, Match and Transpose Results

[andersen_yunan]

Hi, so I got this raw data

Name	Count	Score
Andrew	5	98	99	76	82
Christine	2	56	88
Randall	5	82	88	82	76
Kylie	5	54	66	63	77

<colgroup><col width="64" style="width:48pt" span="6"> </colgroup><tbody>
</tbody>

and I want to make it like this (the name will have drop down list which would automatically change the result if I choose different name of people)

Andrew

98

99

76

82

<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>
</tbody>

Is there any excel formula that I could use to meet the results? I'm thinking of using index, match and transpose but couldn't find better way to solve this
Thanks in advance!

 

[Robby19]

In my opinion a Pivot table would be best suited for this. This would give you the dropdown and sorting options needed.

 

[Marcelo Branco]

Shouldn't the values in column B (Count) be 4; 2; 4; 4 instead of 5; 2; 5; 5?

If so, try

	A​	B​	C​	D​	E​	F​
1​	Name​	Count​	Score​
2​	Andrew​	4​	98​	99​	76​	82​
3​	Christine​	2​	56​	88​
4​	Randall​	4​	82​	88​	82​	76​
5​	Kylie​	4​	54​	66​	63​	77​
6​
7​
8​	Andrew​
9​	98​
10​	99​
11​	76​
12​	82​
13​

Array formula in A9 copied down
=IF(INDEX(B$2:B$5,MATCH(A$8,A$2:A$5,0))>=ROWS(A$9:A9),INDEX(INDEX(C$2:F$5,MATCH(A$8,A$2:A$5,0),0),ROWS(A$9:A9)),"")
Ctrl+Shift+Enter

Hope this helps

M.

 

[sheetspread]

Excel 2010
	A	B	C	D	E	F
1	Name	Count	Score
2	Andrew	5	98	99	76	82
3	Christine	2	56	88
4	Randall	5	82	88	82	76
5	Kylie	5	54	66	63	77
6
7
8	Andrew
9	98
10	99
11	76
12	82
Sheet7

Cell Formulas
Range	Formula
A9:A12	{=INDEX(TRANSPOSE($C$2:$F$5),,MATCH($A$8,$A$2:$A$5,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[sheetspread]

=OFFSET(INDEX($C$2:$C$5,MATCH($A$8,$A$2:$A$5,0)),0,ROW(A1)-1)

in A9 copied down works too

 

[Marcelo Branco]

=OFFSET(INDEX($C$2:$C$5,MATCH($A$8,$A$2:$A$5,0)),0,ROW(A1)-1)

in A9 copied down works too

Yes, this formula is simple and works, but it shows 0 in A11 and A12 for Christine - not sure this is acceptable.
Let's wait the OP.

M.

 

[sheetspread]

This gets rid of the 0s:

=OFFSET(INDEX($C$2:$C$5,MATCH($A$8,$A$2:$A$5,0)),0,ROW(A1)-1)&""

but it is a good idea to wait for him to respond (and explain the count of 5 instead of 4)

 

[andersen_yunan]

Hi guys!
These formula works perfectly thanks a lot for your help!