How to convert table to column data

[Rock5150]

I have the below grid table of data with X and Y axis information. I would like to only pull the numbers where the X and Y names are intersecting and from a subset of information. So in one sheet (image 1) i have the below information. On another sheet (image 2) i have the names of the players that i want to pull. The reason for this second sheet is it is hard for me to scan a grid table to find the lowest score with the players eligible (image 2). I have a conditional format (from another thread) that colors those in sheet 1 that are playing (sheet 2). However i would like sheet two to have the third column populate the net score so then in column c (sheet 2, third column) i can do a quick sort

SHEET/IMAGE 1. This tab is called "Scores"

Ham and Egg Spreadsheet.xlsx
	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1		Alston Jason	Archuleta Michael	Baker Bea	Bledsoe Darrell	Bolden Anthony	Booker Richard	Brister Antonio	Brown Kendall	Colebrook James	Collebrook Sean	Dubose Damitoin	Edmonds Jojo	Edmonds Scott	Evans Booker
2	Alston Jason		67	55	77	72	64	68	72	69	72	70	62	56	59
3	Archuleta Michael	67		55	72	67	62	68	71	68	67	69	60	54	59
4	Baker Bea	55	55		55	55	51	54	55	55	54	55	50	47	49
5	Bledsoe Darrell	77	72	55		76	67	76	82	74	76	74	64	56	61
6	Bolden Anthony	72	67	55	76		63	69	74	69	70	68	61	55	59
7	Booker Richard	64	62	51	67	63		62	66	62	63	64	57	52	55
8	Brister Antonio	68	68	54	76	69	62		74	70	70	68	60	54	60
9	Brown Kendall	72	71	55	82	74	66	74		74	74	72	62	55	59
10	Colebrook James	69	68	55	74	69	62	70	74		70	68	59	54	60
11	Collebrook Sean	72	67	54	76	70	63	70	74	70		70	62	56	60
12	Dubose Damitoin	70	69	55	74	68	64	68	72	68	70		58	54	60
13	Edmonds Jojo	62	60	50	64	61	57	60	62	59	62	58		53	54
14	Edmonds Scott	56	54	47	56	55	52	54	55	54	56	54	53		50
15	Evans Booker	59	59	49	61	59	55	60	59	60	60	60	54	50
Scores

Cell Formulas
Range	Formula
X2:AK15	X2	=IF($W2=X$1,"",LET(tbl,$B$5:$S$10983,n,$A$5:$A$10983,r,INDEX(tbl,MATCH($W2,n,0)+1,0),c,INDEX(tbl,MATCH(X$1,n,0)+1,0),SUM(IF(r<c,r,c))))

Cells with Conditional Formatting
Cell	Condition		Cell Format	Stop If True
X103:EJ213,X81:X102,X2:EJ80	Expression	=COUNTIFS(Names!$A$2:$A$500,$W2,Names!$B$2:$B$500,X$1)	text	NO

SHEET/IMAGE 2 . The third column is where i want to populate the net score pulled from sheet/image 1. This tab is called "Names"

Ham and Egg Spreadsheet.xlsx
	A	B	C
1	Partner A	Partner B	NET SCORE
2	Alston Jason	Alston Jason
3	Alston Jason	Archuleta Michael
4	Alston Jason	Baker Bea
5	Alston Jason	Bledsoe Darrell
6	Alston Jason	Bolden Anthony
7	Alston Jason	Booker Richard
8	Alston Jason	Brister Antonio
9	Alston Jason	Brown Kendall
10	Alston Jason	Colebrook James
11	Alston Jason	Collebrook Sean
12	Alston Jason	Dubose Damitoin
13	Alston Jason	Edmonds Jojo
14	Alston Jason	Edmonds Scott
15	Alston Jason	Evans Booker
16	Evans Booker	Edmonds Jojo
17	Evans Booker	Baker Bea
18	Bolden Anthony	Baker Bea
19	Brown Kendall	Archuleta Michael
Names

 

[Rock5150]

To follow up, in teh above, Column A in sheet 2 would be the "vertical" in Column w (sheet 1) and the Column B in sheet 2 above would be the horizontal (row 1)

 

[JamesCanale]

So you wanted to look up the net score from the various match-ups from the big table?:

Mr Excel Playground 3.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK
1	Partner A	Partner B	NET SCORE																					Alston Jason	Archuleta Michael	Baker Bea	Bledsoe Darrell	Bolden Anthony	Booker Richard	Brister Antonio	Brown Kendall	Colebrook James	Collebrook Sean	Dubose Damitoin	Edmonds Jojo	Edmonds Scott	Evans Booker
2	Alston Jason	Alston Jason	0																				Alston Jason		51	29	85	5	4	44	83	12	84	42	76	85	60
3	Alston Jason	Archuleta Michael	51																				Archuleta Michael	30		65	40	94	23	12	50	84	60	36	99	32	76
4	Alston Jason	Baker Bea	29																				Baker Bea	34	17		52	77	65	89	14	42	36	12	79	84	21
5	Alston Jason	Bledsoe Darrell	85																				Bledsoe Darrell	100	42	36		64	12	81	84	66	62	31	53	40	29
6	Alston Jason	Bolden Anthony	5																				Bolden Anthony	53	2	61	48		66	44	83	92	51	74	10	76	32
7	Alston Jason	Booker Richard	4																				Booker Richard	50	44	58	58	89		55	73	83	46	99	12	94	28
8	Alston Jason	Brister Antonio	44																				Brister Antonio	52	77	58	8	28	67		15	34	38	4	23	74	62
9	Alston Jason	Brown Kendall	83																				Brown Kendall	40	12	37	13	93	65	29		78	98	89	98	88	21
10	Alston Jason	Colebrook James	12																				Colebrook James	19	10	31	30	28	28	14	29		49	50	52	47	22
11	Alston Jason	Collebrook Sean	84																				Collebrook Sean	74	62	70	84	56	53	24	61	68		9	21	71	6
12	Alston Jason	Dubose Damitoin	42																				Dubose Damitoin	39	45	42	83	100	57	51	86	10	100		1	94	96
13	Alston Jason	Edmonds Jojo	76																				Edmonds Jojo	95	46	21	57	76	8	91	45	66	81	5		40	55
14	Alston Jason	Edmonds Scott	85																				Edmonds Scott	13	6	76	73	68	4	23	36	30	82	55	9		28
15	Alston Jason	Evans Booker	60																				Evans Booker	50	49	3	42	2	63	50	65	9	83	72	66	14
16	Evans Booker	Edmonds Jojo	66
17	Evans Booker	Baker Bea	3
18	Bolden Anthony	Baker Bea	61
19	Brown Kendall	Archuleta Michael	12
Sheet21

Cell Formulas
Range	Formula
C2:C19	C2	=INDEX($X$2:$AK$15,MATCH(A2,$W$2:$W$15),MATCH(B2,$X$1:$AK$1))

 

[Fluff]

How about

=INDEX(Scores!$X$2:$AK$15,MATCH(A2,Scores!$W$2:$W$15,0),MATCH(B2,Scores!$X$1:$AK$1,0))

 

[Rock5150]

Yes thank you!!!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[alansidman]

An alternative solution is to unpivot your data using Power Query

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Column1", type text}, {"Alston Jason", Int64.Type}, {"Archuleta Michael", Int64.Type}, {"Baker Bea", Int64.Type}, {"Bledsoe Darrell", Int64.Type}, {"Bolden Anthony", Int64.Type}, {"Booker Richard", Int64.Type}, {"Brister Antonio", Int64.Type}, {"Brown Kendall", Int64.Type}, {"Colebrook James", Int64.Type}, {"Collebrook Sean", Int64.Type}, {"Dubose Damitoin", Int64.Type}, {"Edmonds Jojo", Int64.Type}, {"Edmonds Scott", Int64.Type}, {"Evans Booker", Int64.Type}}),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Column1"}, "Attribute", "Value")
in
    #"Unpivoted Other Columns"