Index, Match and Transpose Results

[andersen_yunan]

Hi, so I got this raw data

[TABLE="width: 384"]
<colgroup><col width="64" style="width:48pt" span="6"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: transparent"]Name[/TD]
[TD="width: 64, bgcolor: transparent"]Count[/TD]
[TD="width: 64, bgcolor: transparent"]Score[/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[TD="width: 64, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Andrew[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]98[/TD]
[TD="bgcolor: transparent, align: right"]99[/TD]
[TD="bgcolor: transparent, align: right"]76[/TD]
[TD="bgcolor: transparent, align: right"]82[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Christine[/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent, align: right"]56[/TD]
[TD="bgcolor: transparent, align: right"]88[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Randall[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]82[/TD]
[TD="bgcolor: transparent, align: right"]88[/TD]
[TD="bgcolor: transparent, align: right"]82[/TD]
[TD="bgcolor: transparent, align: right"]76[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Kylie[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent, align: right"]54[/TD]
[TD="bgcolor: transparent, align: right"]66[/TD]
[TD="bgcolor: transparent, align: right"]63[/TD]
[TD="bgcolor: transparent, align: right"]77[/TD]
[/TR]
</tbody>[/TABLE]

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)
[TABLE="width: 64"]
<colgroup><col width="64" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64, bgcolor: #FFFF00"]Andrew[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]98[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]99[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]76[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]82[/TD]
[/TR]
</tbody>[/TABLE]

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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Name​

[/td][td]

Count​

[/td][td]

Score​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

Andrew​

[/td][td]

4​

[/td][td]

98​

[/td][td]

99​

[/td][td]

76​

[/td][td]

82​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Christine​

[/td][td]

2​

[/td][td]

56​

[/td][td]

88​

[/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Randall​

[/td][td]

4​

[/td][td]

82​

[/td][td]

88​

[/td][td]

82​

[/td][td]

76​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Kylie​

[/td][td]

4​

[/td][td]

54​

[/td][td]

66​

[/td][td]

63​

[/td][td]

77​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

8​

[/td][td]

Andrew​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

9​

[/td][td]

98​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

10​

[/td][td]

99​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

11​

[/td][td]

76​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

12​

[/td][td]

82​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

13​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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!