Matching sheets together

Steviefiege

Board Regular
Joined
Aug 29, 2018
Messages
66
Hi,
Below is my main data table of all my players:

Sheet 1

NameTotal Rat.CorCroDriFinFirFreHeaLonL ThMarPasPenTckTecAggAntBraCmpCntDecDetFlaLdrOtBPosTeaVisWorAccAgiBalJumNatPacStaStr
James Coppinger403101415121411471814101015713111212101413151410151514811147148147
Andy Boyle3895745103144714129141212131413131316414814139141110131414101413
Carlton Cole3896111012951499111212119101111812912129128171110118141512121015
Tommy Rowe3881011101211107116911812119121212121210111210111410139121191591410
John Marquis388469131111137561010111114101410111015912128158151210121412121313
Mathieu Baudry380661191381484141061411814161387139991112911912141413101411
Craig Alcock3798969108139912961491610141111914212910148141110111015111412
Danny Andrew37612121071110610121112112128101011121012591112141114121011812121311
Matty Blair37471212101069831010610108108111010141571210121117121110815131611
Ben Whiteman3605999710697912111010813111213111151081112111499101212111410
Jordan Houghton3588799118885101210101212101212711969101015101310101091310149
Rodney Kongolo35256979710571110813111211131011954971114712141113146131513
Liam Mandeville351681013111181245111451141010139111510312710121010121281410119
Andrew Williams3434891211510746910497118131091499131210111410111111129129
Luke McCullough341249312913361413513105121313101132144121071213891415131312
Alfie May3415891310811103491249811141079161181279715131498812126
Niall Mason3408117781011810121141191081010101151711101171312911912111312
Andy Butler338343682162513841571412181211111821571214614451215861115
Joe Wright325546494125611931191191399101259910111012107111511101212
Cedric Evina3217899736457658968988891091191081515141181514158
Alfie Beestin3218111191373624128611108599916132115101312910143119139
Mitchell Lund31939931158321094107411811101110541212111312109131212121311
Tom Anderson3186635841461013839598141191010210711136129710161091312
Issam Ben Khemis31766121011757361189114971110831151099111112111291113118
Danny Amos3129101181088711797119109656567699991312886151487
Shane Blaney299664311692381059109810681691791195101311101315999
Will Longbottom2925810131049922127611875106112101111010111111810771297
Alex Kiwomya2914888743924763101285787121351247510191296141995
Ali Suljic28268567288887788108888741658107111291212912811
Reece Fielding27963558213251082116108111010983971011710664131071014
Tyler Walker2462321718267621178988713615468581411811141377
Myron Gibbons22543812104134112517710565557813445510957111153
Rieves Boocock212431011649313541778211572711132455117537667
Cameron Barnett2053110121119411131777143878109234412104871075
Branden Horton17111111171110418172645124191113349841213545

<tbody>
</tbody>

On Sheet 2 i have individual positions:

Sheet 2

WB (Right)CroMarPasTckCntDecPosTeaWorSta
BPDFirHeaMarPasTckTecAggAntBraCmpCntDecDetPosVisJumStr
WB (Left)CroMarPasTckCntDecPosTeaWorSta
CMFirHeaMarPasTckCntDecDetPosTeaWorSta
BWMMarTckAggBraDetPosTeaWorAccStaStr
CarrileroTckAntDecPosWorAccNatPacStaStr
CFDriFinFirHeaLonPasTecAntCmpCntDecDetOtBTeaVisWorAccAgiBalJumStr

<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?
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,111
Office Version
  1. 365
Platform
  1. Windows
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.
 
Last edited:

Steviefiege

Board Regular
Joined
Aug 29, 2018
Messages
66

ADVERTISEMENT

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

Steviefiege

Board Regular
Joined
Aug 29, 2018
Messages
66
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-known Member
Joined
Oct 20, 2009
Messages
8,111
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Board Regular
Joined
Aug 29, 2018
Messages
66
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?
 

Steviefiege

Board Regular
Joined
Aug 29, 2018
Messages
66
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.
AerCmdComEccFirHanKic1v1PasRefRusPunThrAggAntBraCmpCntDecDetFlaLdrOtBPosTeaVisWorAccAgiBalJumNatPacStaStr
Lawlor, Ian
331
13131357121113812991181012121110938810108871071410848
Marosi, Marko 306
10108101121177118799101389149392148771210310121376

<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

AerCmdComFirHanKic1v1RefThrAntDecPosAgi

<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?
 

Watch MrExcel Video

Forum statistics

Threads
1,109,474
Messages
5,529,052
Members
409,849
Latest member
J7House1984
Top