Matching 2 Cells to get the 3rd

Steviefiege

Board Regular
Joined
Aug 29, 2018
Messages
66
Hi, i'm hoping this is an easy one, i just can't get my head around the formula to use.
Below is my spreadsheet with all my players on the left & every position they play in & their ratings.
On the right is every individual player & their BEST rating. What i'm wanting is for on the right under BEST POSITION is to match the player & the rating to get their BEST POSITION.
For example: Aaron Taylor-Sinclair his best rating is 59.29%, under BEST POSITION i want it to say CWB (Left) as thats his best position but i cant get the formula right. I've put it in bold on the left as so you can see which one i mean.

PLAYERPOSITIONRATINGPLAYERRATINGBEST POSITION
Frazer RichardsonCWB (Right)Aaron Taylor-Sinclair59.29%
Gary Taylor-FletcherAVP66.67%Andrew Williams
Paul KeeganAVP65.00%Andy Butler
Gary Taylor-FletcherDLP63.89%Benjamin Greasley
Mathieu BaudryBPD62.35%Cedric Evina
Paul KeeganDLP62.22%Conner Williamson
Tommy RoweCWB (Left)61.67%Craig Alcock
Tommy RoweAVP61.11%Dany N'Guessan
Andy ButlerBPD60.88%David Foley
Andrew WilliamsWinger (Right)60.45%Dean Winnard
Gary Taylor-FletcherCF60.23%Frazer Richardson
Craig AlcockBPD60.00%Gary McSheffrey
Aaron Taylor-Sinclair
CWB (Left)59.29%Gary Taylor-Fletcher
Tommy RoweIF (Left)59.29%Harry Middleton
Aaron Taylor-SinclairBPD59.12%Jai Quitongo
Tommy RoweDLP58.33%Joe Pugh
Gary Taylor-FletcherWinger (Right)58.18%Joe Wright
Cedric EvinaCWB (Left)58.10%John Marquis
Andrew WilliamsIF (Left)56.79%Jordan Houghton
Jordan HoughtonDLP56.67%Jordan Linley
David FoleyWinger (Right)56.36%Joseph McCormick

<tbody>
</tbody>

Kind Regards
Steve
 

Some videos you may like

Excel Facts

Control Word Wrap
Press Alt+Enter to move to a new row in a cell. Lets you control where the words wrap.

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

This is one way, Array Formula to be confirmed by CSE (Control, Shift, Enter):

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">PLAYER</td><td style=";">POSITION</td><td style=";">RATING</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">PLAYER</td><td style="text-align: right;;"></td><td style=";">RATING</td><td style=";">BEST POSITION</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Frazer Richardson</td><td style=";">CWB (Right)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Aaron Taylor-Sinclair</td><td style="text-align: right;;"></td><td style="text-align: right;;">59.29%</td><td style=";">CWB (Left)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Gary Taylor-Fletcher</td><td style=";">AVP</td><td style="text-align: right;;">66.67%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Andrew Williams</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Paul Keegan</td><td style=";">AVP</td><td style="text-align: right;;">65.00%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Andy Butler</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Gary Taylor-Fletcher</td><td style=";">DLP</td><td style="text-align: right;;">63.89%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Benjamin Greasley</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Mathieu Baudry</td><td style=";">BPD</td><td style="text-align: right;;">62.35%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Cedric Evina</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Paul Keegan</td><td style=";">DLP</td><td style="text-align: right;;">62.22%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Conner Williamson</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Tommy Rowe</td><td style=";">CWB (Left)</td><td style="text-align: right;;">61.67%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Craig Alcock</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Tommy Rowe</td><td style=";">AVP</td><td style="text-align: right;;">61.11%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Dany N'Guessan</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Andy Butler</td><td style=";">BPD</td><td style="text-align: right;;">60.88%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">David Foley</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Andrew Williams</td><td style=";">Winger (Right)</td><td style="text-align: right;;">60.45%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Dean Winnard</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Gary Taylor-Fletcher</td><td style=";">CF</td><td style="text-align: right;;">60.23%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Frazer Richardson</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Craig Alcock</td><td style=";">BPD</td><td style="text-align: right;;">60.00%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Gary McSheffrey</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Aaron Taylor-Sinclair</td><td style=";">CWB (Left)</td><td style="text-align: right;;">59.29%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Gary Taylor-Fletcher</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Tommy Rowe</td><td style=";">IF (Left)</td><td style="text-align: right;;">59.29%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Harry Middleton</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Aaron Taylor-Sinclair</td><td style=";">BPD</td><td style="text-align: right;;">59.12%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Jai Quitongo</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Tommy Rowe</td><td style=";">DLP</td><td style="text-align: right;;">58.33%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Joe Pugh</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Gary Taylor-Fletcher</td><td style=";">Winger (Right)</td><td style="text-align: right;;">58.18%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Joe Wright</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Cedric Evina</td><td style=";">CWB (Left)</td><td style="text-align: right;;">58.10%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">John Marquis</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">Andrew Williams</td><td style=";">IF (Left)</td><td style="text-align: right;;">56.79%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Jordan Houghton</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">Jordan Houghton</td><td style=";">DLP</td><td style="text-align: right;;">56.67%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Jordan Linley</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">David Foley</td><td style=";">Winger (Right)</td><td style="text-align: right;;">56.36%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Joseph McCormick</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet214</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J3</th><td style="text-align:left">{=INDEX(<font color="Blue">B3:B23,MATCH(<font color="Red">G3&I3,A3:A23&C3:C23,0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Steviefiege

Board Regular
Joined
Aug 29, 2018
Messages
66
Regarding my spreadsheet above. I can't remember how i did the formula in the right hand column for the rating (59.29%). It's just literally getting the highest number on the left hand column for that particular player. Can anyone help please?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
That's also going to require an Array formula:

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">PLAYER</td><td style=";">POSITION</td><td style=";">RATING</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">PLAYER</td><td style="text-align: right;;"></td><td style=";">RATING</td><td style=";">BEST POSITION</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Frazer Richardson</td><td style=";">CWB (Right)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Aaron Taylor-Sinclair</td><td style="text-align: right;;"></td><td style="text-align: right;;">59.29%</td><td style=";">CWB (Left)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Gary Taylor-Fletcher</td><td style=";">AVP</td><td style="text-align: right;;">66.67%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Andrew Williams</td><td style="text-align: right;;"></td><td style="text-align: right;;">60.45%</td><td style=";">Winger (Right)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Paul Keegan</td><td style=";">AVP</td><td style="text-align: right;;">65.00%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Andy Butler</td><td style="text-align: right;;"></td><td style="text-align: right;;">60.88%</td><td style=";">BPD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Gary Taylor-Fletcher</td><td style=";">DLP</td><td style="text-align: right;;">63.89%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Benjamin Greasley</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Mathieu Baudry</td><td style=";">BPD</td><td style="text-align: right;;">62.35%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Cedric Evina</td><td style="text-align: right;;"></td><td style="text-align: right;;">58.10%</td><td style=";">CWB (Left)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Paul Keegan</td><td style=";">DLP</td><td style="text-align: right;;">62.22%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Conner Williamson</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Tommy Rowe</td><td style=";">CWB (Left)</td><td style="text-align: right;;">61.67%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Craig Alcock</td><td style="text-align: right;;"></td><td style="text-align: right;;">60.00%</td><td style=";">BPD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Tommy Rowe</td><td style=";">AVP</td><td style="text-align: right;;">61.11%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Dany N'Guessan</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Andy Butler</td><td style=";">BPD</td><td style="text-align: right;;">60.88%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">David Foley</td><td style="text-align: right;;"></td><td style="text-align: right;;">56.36%</td><td style=";">Winger (Right)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Andrew Williams</td><td style=";">Winger (Right)</td><td style="text-align: right;;">60.45%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Dean Winnard</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Gary Taylor-Fletcher</td><td style=";">CF</td><td style="text-align: right;;">60.23%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Frazer Richardson</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Craig Alcock</td><td style=";">BPD</td><td style="text-align: right;;">60.00%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Gary McSheffrey</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Aaron Taylor-Sinclair</td><td style=";">CWB (Left)</td><td style="text-align: right;;">59.29%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Gary Taylor-Fletcher</td><td style="text-align: right;;"></td><td style="text-align: right;;">66.67%</td><td style=";">AVP</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Tommy Rowe</td><td style=";">IF (Left)</td><td style="text-align: right;;">59.29%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Harry Middleton</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">Aaron Taylor-Sinclair</td><td style=";">BPD</td><td style="text-align: right;;">59.12%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Jai Quitongo</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Tommy Rowe</td><td style=";">DLP</td><td style="text-align: right;;">58.33%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Joe Pugh</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">Gary Taylor-Fletcher</td><td style=";">Winger (Right)</td><td style="text-align: right;;">58.18%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Joe Wright</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Cedric Evina</td><td style=";">CWB (Left)</td><td style="text-align: right;;">58.10%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">John Marquis</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">Andrew Williams</td><td style=";">IF (Left)</td><td style="text-align: right;;">56.79%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Jordan Houghton</td><td style="text-align: right;;"></td><td style="text-align: right;;">56.67%</td><td style=";">DLP</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">Jordan Houghton</td><td style=";">DLP</td><td style="text-align: right;;">56.67%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Jordan Linley</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">#N/A</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">David Foley</td><td style=";">Winger (Right)</td><td style="text-align: right;;">56.36%</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">Joseph McCormick</td><td style="text-align: right;;"></td><td style="text-align: right;;">0.00%</td><td style="text-align: right;;">#N/A</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet214</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">I3</th><td style="text-align:left">{=MAX(<font color="Blue">IF(<font color="Red">A$3:A$23=G3,C$3:C$23</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">J3</th><td style="text-align:left">{=INDEX(<font color="Blue">B$3:B$23,MATCH(<font color="Red">G3&I3,A$3:A$23&C$3:C$23,0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
 

Steviefiege

Board Regular
Joined
Aug 29, 2018
Messages
66
Thank you so much for that, kind of getting there now but still stuck on some more bits. See if i can explain this...
This is my page which automatically picks my squad Sheet 1 (well it will be when its done!)
I've sorted the formula for the rating (58.50). What i want is to match it to the player with that rating in that position which i have got on another sheet, Sheet 2. I'll add it underneath.

Sheet 1

Position
Player
Rating



WB (Right)
58.50
CD

CD

WB (Left)




BBM
BBM




IF (Right)


IF (Left)





F9
F9

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet 2

Alex Kiwomya
49.64 IF (Right)
Alfie Beestin
52.14 IF (Right)
Alfie May
50.71 IF (Left)
Andrew Williams53.46 F9
Andy Boyle68.33 CD
Andy Butler70.83 CD
Anthony Greaves30.00 IF (Right)
Ben Whiteman54.21 BBM
Branden Horton46.25 CD
Cameron Barnett36.92 F9
Cedric Evina47.00
WB (Left)
Corie Andrews50.00 IF (Right)
Craig Alcock60.42 CD
Danny Andrew61.00
WB (Left)
Henrik Bjordal55.53 BBM
Issam Ben Khemis48.95 BBM
James Coppinger60.71 IF (Right)
Joe Wright56.25 CD
John Marquis54.23 F9
Jordan Houghton51.58 BBM
Liam Mandeville56.92 F9
Luke McCullough57.92 CD
Mathieu Baudry60.00 CD
Matty Blair
58.50
WB (Right)
Mitchell Lund53.50 WB (Right)
Myron Gibbons32.69 F9
Niall Mason56.50 WB (Right)
Rieves Boocock36.15 F9
Rodney Kongolo57.08 CD
Shane Blaney49.58 CD
Tom Anderson56.25 CD
Tommy Rowe59.50
WB (Left)
Tyler Garrett54.00 WB (Left)

<colgroup><col><col><col></colgroup><tbody>
</tbody>


The player in bold in sheet 2 is the player that wants to be in sheet 1 in the WB (Right) position, i just cant get my head around the formula.

Rgs
Steve



<colgroup><col><col><col></colgroup><tbody>
</tbody>
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
Hi,

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Position</td><td style="text-align: right;;"></td><td style=";">Player</td><td style="text-align: right;;"></td><td style=";">Rating</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">WB (Right)</td><td style="text-align: right;;"></td><td style=";">Matty Blair</td><td style="text-align: right;;"></td><td style="text-align: right;;">58.5</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">CD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">CD</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">WB (Left)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">BBM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">BBM</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">IF (Right)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">IF (Left)</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">F9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">F9</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet220</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: rgb(255,255,255)" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: rgb(255,255,255);border-collapse: collapse; border-color: rgb(187,187,187)"><thead><tr style=" background-color: rgb(218,231,245);color: rgb(22,17,32)"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: rgb(218,231,245);color: rgb(22,17,32)">C3</th><td style="text-align:left">{=INDEX(<font color="Blue">Sheet221!A$1:A$33,MATCH(<font color="Red">A$3&E$3,Sheet221!C$1:C$33&Sheet221!B$1:B$33,0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

<b></b><table cellpadding="2.5px" rules="all" style=";background-color: rgb(255,255,255);border: 1px solid;border-collapse: collapse; border-color: rgb(187,187,187)"><colgroup><col width="25px" style="background-color: rgb(218,231,245)" /><col /><col /><col /></colgroup><thead><tr style=" background-color: rgb(218,231,245);text-align: center;color: rgb(22,17,32)"><th></th><th>A</th><th>B</th><th>C</th></tr></thead><tbody><tr ><td style="color: rgb(22,17,32);text-align: center;">1</td><td style=";">Alex Kiwomya</td><td style="text-align: right;;">49.64</td><td style=";">IF (Right)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">2</td><td style=";">Alfie Beestin</td><td style="text-align: right;;">52.14</td><td style=";">IF (Right)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">3</td><td style=";">Alfie May</td><td style="text-align: right;;">50.71</td><td style=";">IF (Left)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">4</td><td style=";">Andrew Williams</td><td style="text-align: right;;">53.46</td><td style=";">F9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">5</td><td style=";">Andy Boyle</td><td style="text-align: right;;">68.33</td><td style=";">CD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">6</td><td style=";">Andy Butler</td><td style="text-align: right;;">70.83</td><td style=";">CD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">7</td><td style=";">Anthony Greaves</td><td style="text-align: right;;">30</td><td style=";">IF (Right)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">8</td><td style=";">Ben Whiteman</td><td style="text-align: right;;">54.21</td><td style=";">BBM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">9</td><td style=";">Branden Horton</td><td style="text-align: right;;">46.25</td><td style=";">CD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">10</td><td style=";">Cameron Barnett</td><td style="text-align: right;;">36.92</td><td style=";">F9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">11</td><td style=";">Cedric Evina</td><td style="text-align: right;;">47</td><td style=";">WB (Left)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">12</td><td style=";">Corie Andrews</td><td style="text-align: right;;">50</td><td style=";">IF (Right)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">13</td><td style=";">Craig Alcock</td><td style="text-align: right;;">60.42</td><td style=";">CD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">14</td><td style=";">Danny Andrew</td><td style="text-align: right;;">61</td><td style=";">WB (Left)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">15</td><td style=";">Henrik Bjordal</td><td style="text-align: right;;">55.53</td><td style=";">BBM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">16</td><td style=";">Issam Ben Khemis</td><td style="text-align: right;;">48.95</td><td style=";">BBM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">17</td><td style=";">James Coppinger</td><td style="text-align: right;;">60.71</td><td style=";">IF (Right)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">18</td><td style=";">Joe Wright</td><td style="text-align: right;;">56.25</td><td style=";">CD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">19</td><td style=";">John Marquis</td><td style="text-align: right;;">54.23</td><td style=";">F9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">20</td><td style=";">Jordan Houghton</td><td style="text-align: right;;">51.58</td><td style=";">BBM</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">21</td><td style=";">Liam Mandeville</td><td style="text-align: right;;">56.92</td><td style=";">F9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">22</td><td style=";">Luke McCullough</td><td style="text-align: right;;">57.92</td><td style=";">CD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">23</td><td style=";">Mathieu Baudry</td><td style="text-align: right;;">60</td><td style=";">CD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">24</td><td style=";">Matty Blair</td><td style="text-align: right;;">58.5</td><td style=";">WB (Right)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">25</td><td style=";">Mitchell Lund</td><td style="text-align: right;;">53.5</td><td style=";">WB (Right)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">26</td><td style=";">Myron Gibbons</td><td style="text-align: right;;">32.69</td><td style=";">F9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">27</td><td style=";">Niall Mason</td><td style="text-align: right;;">56.5</td><td style=";">WB (Right)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">28</td><td style=";">Rieves Boocock</td><td style="text-align: right;;">36.15</td><td style=";">F9</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">29</td><td style=";">Rodney Kongolo</td><td style="text-align: right;;">57.08</td><td style=";">CD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">30</td><td style=";">Shane Blaney</td><td style="text-align: right;;">49.58</td><td style=";">CD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">31</td><td style=";">Tom Anderson</td><td style="text-align: right;;">56.25</td><td style=";">CD</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">32</td><td style=";">Tommy Rowe</td><td style="text-align: right;;">59.5</td><td style=";">WB (Left)</td></tr><tr ><td style="color: rgb(22,17,32);text-align: center;">33</td><td style=";">Tyler Garrett</td><td style="text-align: right;;">54</td><td style=";">WB (Left)</td></tr></tbody></table><p style="width:6.4em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid rgb(187,187,187);border-top:none;text-align: center;background-color: rgb(218,231,245);color: rgb(22,17,32)">Sheet221</p><br /><br />

Change cell references/range and sheet name to match your data.
 

Steviefiege

Board Regular
Joined
Aug 29, 2018
Messages
66
Thank you soo soo very much. Another question, Where i have 2 x CD & 2 x BBM & 2 x F9, how do i get the 2nd highest rated player there?
 

jtakw

Well-known Member
Joined
Jun 29, 2014
Messages
5,146
That's going to require a different formula.
So your actual setup is like my Post # 8 above?
Are there actually empty rows (in between different positions) as shown in Post # 8, or not?
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,099,030
Messages
5,466,121
Members
406,468
Latest member
Toto Li

This Week's Hot Topics

Top