Matching sheets together

[Steviefiege]

Hi,
Below is my main data table of all my players:

Sheet 1

Name	Total Rat.	Cor	Cro	Dri	Fin	Fir	Fre	Hea	Lon	L Th	Mar	Pas	Pen	Tck	Tec	Agg	Ant	Bra	Cmp	Cnt	Dec	Det	Fla	Ldr	OtB	Pos	Tea	Vis	Wor	Acc	Agi	Bal	Jum	Nat	Pac	Sta	Str
James Coppinger	403	10	14	15	12	14	11	4	7	1	8	14	10	10	15	7	13	11	12	12	10	14	13	15	14	10	15	15	14	8	11	14	7	14	8	14	7
Andy Boyle	389	5	7	4	5	10	3	14	4	7	14	12	9	14	12	12	13	14	13	13	13	16	4	14	8	14	13	9	14	11	10	13	14	14	10	14	13
Carlton Cole	389	6	11	10	12	9	5	14	9	9	11	12	12	11	9	10	11	11	8	12	9	12	12	9	12	8	17	11	10	11	8	14	15	12	12	10	15
Tommy Rowe	388	10	11	10	12	11	10	7	11	6	9	11	8	12	11	9	12	12	12	12	12	10	11	12	10	11	14	10	13	9	12	11	9	15	9	14	10
John Marquis	388	4	6	9	13	11	11	13	7	5	6	10	10	11	11	14	10	14	10	11	10	15	9	12	12	8	15	8	15	12	10	12	14	12	12	13	13
Mathieu Baudry	380	6	6	11	9	13	8	14	8	4	14	10	6	14	11	8	14	16	13	8	7	13	9	9	9	11	12	9	11	9	12	14	14	13	10	14	11
Craig Alcock	379	8	9	6	9	10	8	13	9	9	12	9	6	14	9	16	10	14	11	11	9	14	2	12	9	10	14	8	14	11	10	11	10	15	11	14	12
Danny Andrew	376	12	12	10	7	11	10	6	10	12	11	12	1	12	12	8	10	10	11	12	10	12	5	9	11	12	14	11	14	12	10	11	8	12	12	13	11
Matty Blair	374	7	12	12	10	10	6	9	8	3	10	10	6	10	10	8	10	8	11	10	10	14	15	7	12	10	12	11	17	12	11	10	8	15	13	16	11
Ben Whiteman	360	5	9	9	9	7	10	6	9	7	9	12	11	10	10	8	13	11	12	13	11	11	5	10	8	11	12	11	14	9	9	10	12	12	11	14	10
Jordan Houghton	358	8	7	9	9	11	8	8	8	5	10	12	10	10	12	12	10	12	12	7	11	9	6	9	10	10	15	10	13	10	10	10	9	13	10	14	9
Rodney Kongolo	352	5	6	9	7	9	7	10	5	7	11	10	8	13	11	12	11	13	10	11	9	5	4	9	7	11	14	7	12	14	11	13	14	6	13	15	13
Liam Mandeville	351	6	8	10	13	11	11	8	12	4	5	11	14	5	11	4	10	10	13	9	11	15	10	3	12	7	10	12	10	10	12	12	8	14	10	11	9
Andrew Williams	343	4	8	9	12	11	5	10	7	4	6	9	10	4	9	7	11	8	13	10	9	14	9	9	13	12	10	11	14	10	11	11	11	12	9	12	9
Luke McCullough	341	2	4	9	3	12	9	13	3	6	14	13	5	13	10	5	12	13	13	10	11	3	2	14	4	12	10	7	12	13	8	9	14	15	13	13	12
Alfie May	341	5	8	9	13	10	8	11	10	3	4	9	12	4	9	8	11	14	10	7	9	16	11	8	12	7	9	7	15	13	14	9	8	8	12	12	6
Niall Mason	340	8	11	7	7	8	10	11	8	10	12	11	4	11	9	10	8	10	10	10	11	5	1	7	11	10	11	7	13	12	9	11	9	12	11	13	12
Andy Butler	338	3	4	3	6	8	2	16	2	5	13	8	4	15	7	14	12	18	12	11	11	18	2	15	7	12	14	6	14	4	5	12	15	8	6	11	15
Joe Wright	325	5	4	6	4	9	4	12	5	6	11	9	3	11	9	11	9	13	9	9	10	12	5	9	9	10	11	10	12	10	7	11	15	11	10	12	12
Cedric Evina	321	7	8	9	9	7	3	6	4	5	7	6	5	8	9	6	8	9	8	8	8	9	10	9	11	9	10	8	15	15	14	11	8	15	14	15	8
Alfie Beestin	321	8	11	11	9	13	7	3	6	2	4	12	8	6	11	10	8	5	9	9	9	16	13	2	11	5	10	13	12	9	10	14	3	11	9	13	9
Mitchell Lund	319	3	9	9	3	11	5	8	3	2	10	9	4	10	7	4	11	8	11	10	11	10	5	4	12	12	11	13	12	10	9	13	12	12	12	13	11
Tom Anderson	318	6	6	3	5	8	4	14	6	10	13	8	3	9	5	9	8	14	11	9	10	10	2	10	7	11	13	6	12	9	7	10	16	10	9	13	12
Issam Ben Khemis	317	6	6	12	10	11	7	5	7	3	6	11	8	9	11	4	9	7	11	10	8	3	11	5	10	9	9	11	11	12	11	12	9	11	13	11	8
Danny Amos	312	9	10	11	8	10	8	8	7	11	7	9	7	11	9	10	9	6	5	6	5	6	7	6	9	9	9	9	13	12	8	8	6	15	14	8	7
Shane Blaney	299	6	6	4	3	11	6	9	2	3	8	10	5	9	10	9	8	10	6	8	16	9	1	7	9	11	9	5	10	13	11	10	13	15	9	9	9
Will Longbottom	292	5	8	10	13	10	4	9	9	2	2	12	7	6	11	8	7	5	10	6	11	2	10	1	11	10	10	11	11	11	8	10	7	7	12	9	7
Alex Kiwomya	291	4	8	8	8	7	4	3	9	2	4	7	6	3	10	12	8	5	7	8	7	12	13	5	12	4	7	5	10	19	12	9	6	14	19	9	5
Ali Suljic	282	6	8	5	6	7	2	8	8	8	8	7	7	8	8	10	8	8	8	8	7	4	1	6	5	8	10	7	11	12	9	12	12	9	12	8	11
Reece Fielding	279	6	3	5	5	8	2	13	2	5	10	8	2	11	6	10	8	11	10	10	9	8	3	9	7	10	11	7	10	6	6	4	13	10	7	10	14
Tyler Walker	246	2	3	2	1	7	1	8	2	6	7	6	2	11	7	8	9	8	8	7	13	6	1	5	4	6	8	5	8	14	11	8	11	14	13	7	7
Myron Gibbons	225	4	3	8	12	10	4	13	4	1	1	2	5	1	7	7	10	5	6	5	5	5	7	8	13	4	4	5	5	10	9	5	7	11	11	5	3
Rieves Boocock	212	4	3	10	11	6	4	9	3	1	3	5	4	1	7	7	8	2	11	5	7	2	7	11	13	2	4	5	5	11	7	5	3	7	6	6	7
Cameron Barnett	205	3	1	10	12	11	1	9	4	1	1	1	3	1	7	7	7	1	4	3	8	7	8	10	9	2	3	4	4	12	10	4	8	7	10	7	5
Branden Horton	171	1	1	1	1	1	1	7	1	1	10	4	1	8	1	7	2	6	4	5	12	4	1	9	1	11	3	3	4	9	8	4	12	13	5	4	5

<tbody>
</tbody>

On Sheet 2 i have individual positions:

Sheet 2

WB (Right)

Cro

Mar

Pas

Tck

Cnt

Dec

Pos

Tea

Wor

Sta

BPD

Fir

Hea

Mar

Pas

Tck

Tec

Agg

Ant

Bra

Cmp

Cnt

Dec

Det

Pos

Vis

Jum

Str

WB (Left)

Cro

Mar

Pas

Tck

Cnt

Dec

Pos

Tea

Wor

Sta

CM

Fir

Hea

Mar

Pas

Tck

Cnt

Dec

Det

Pos

Tea

Wor

Sta

BWM

Mar

Tck

Agg

Bra

Det

Pos

Tea

Wor

Acc

Sta

Str

Carrilero

Tck

Ant

Dec

Pos

Wor

Acc

Nat

Pac

Sta

Str

CF

Dri

Fin

Fir

Hea

Lon

Pas

Tec

Ant

Cmp

Cnt

Dec

Det

OtB

Tea

Vis

Wor

Acc

Agi

Bal

Jum

Str

<tbody>
</tbody>

What i'm wanting to do on a separate sheet (sheet 3) is find which players are best in each position. For example, if i put my top rated player in (J.Coppinger) & at the side of his name put CM i want it to look up his attributes from sheet 1 & match them with the attributes listed for CM in sheet 2 then display the results in sheet 3. Is this possible?

 

[Steviefiege]

Can anyone help please?

 

[Steviefiege]

Really need help on this one please as it's racking my brain!

 

[steve the fish]

Ok in Sheet3 A2 place the name. In B2 put 'CM'. In C1:

=IF(VLOOKUP($B2,Sheet2!$A:$V,COLUMNS($A$1:A1)+1,0)="","",VLOOKUP($B2,Sheet2!$A:$V,COLUMNS($A$1:A1)+1,0))

copy across until you dont get any more values.

In C2:

=IFERROR(VLOOKUP($A2,Sheet1!$A:$AL,MATCH(C$1,Sheet1!$A$1:$AL$1,0),0),"")

again copy across.

This assumes both your tables have A1 as the first used cell in their respective sheets.

 

[Steviefiege]

When i put the first formula in i just get #N/A.

 

[Steviefiege]

Got the first bit working, put a wrong cell in.
The 2nd formula though i don't understand the bit in bold as that is just an empty cell isnt it?

=IFERROR(VLOOKUP($A2,Sheet1!$A:$AL,MATCH(C$1,Sheet1!$A$1:$AL$1,0),0),"")

 

[steve the fish]

Well no. In my post I said put a formula in c1. So if it's blank then you haven't done it correctly.

 

[Steviefiege]

Sorry, my mistake, read it through more slowly & it made sense! Just got a little bit of a problem though. Got the formula working lovely thank you, but how do i go about doing it for every player & every position?
Instead of just James Coppinger in CM, how would i do the next player down the list, Andy Boyle, his position is BPD. Because when you drag the formula along its only looking in the CM attributes.

Say in sheet 3 as your example:

A2 B2
James Coppinger CM

& then the formula is in C1 & across
If i put Andy Boyle BPD underneath how would i get it to work?

 

[steve the fish]

Drag the formulas down.

 

[Steviefiege]

Thank you very much Steve, this is working an absolute treat. If you wouldn't mind though, could i ask something else. At the very top of the page on sheet 1 where there are all my players & their individual attributes, i missed something off....my goalkeepers. Their attributes are a little bit different than the rest of the outfield players though. In sheet 1 i have a seperate table for them

Name	Squad Info.	Total Rat.	Aer	Cmd	Com	Ecc	Fir	Han	Kic	1v1	Pas	Ref	Rus	Pun	Thr	Agg	Ant	Bra	Cmp	Cnt	Dec	Det	Fla	Ldr	OtB	Pos	Tea	Vis	Wor	Acc	Agi	Bal	Jum	Nat	Pac	Sta	Str
Lawlor, Ian		331	13	13	13	5	7	12	11	13	8	12	9	9	11	8	10	12	12	11	10	9	3	8	8	10	10	8	8	7	10	7	14	10	8	4	8
Marosi, Marko		306	10	10	8	10	1	12	11	7	7	11	8	7	9	9	10	13	8	9	14	9	3	9	2	14	8	7	7	12	10	3	10	12	13	7	6

<colgroup><col><col><col><col span="2"><col span="2"><col><col><col span="2"><col><col><col><col><col span="23"></colgroup><tbody>
</tbody>

And in sheet 2 its this

Aer

Cmd

Com

Fir

Han

Kic

1v1

Ref

Thr

Ant

Dec

Pos

Agi

<colgroup><col width="64" span="14"></colgroup><tbody>
</tbody>

What i was wondering was, in the formula =IF(VLOOKUP($B2,Sheet2!$A:$V,COLUMNS($A$1:A1)+1,0)="","",VLOOKUP($B2,Sheet2!$A:$V,COLUMNS($A$1:A1)+1,0)) would you be able to put another IF function in there to they that if its a Goalkeeper to look at the Goalkeeper table & not the Outfield player table?