Easier way to matchup data

[Pezgordo]

I have a worksheet that contains a row for each of the 120 college football teams. There are 34 columns in the worksheet containing various stats. Each week I cut and paste the teams that are matched up against each other. This is very time consuming. Is there a way that I can write the team name into the first cell and all the information for that team will appear in that row?

For example, Missouri plays Arizona State this Friday. For me to matchup the two teams I have to cut and paste the information from ASU (located on row # 14) to row # 8. I than have to locate Missouri (row # 40) and cut and paste it to row # 9. Is there anyway I can just go into cell A8 and write ASU (or something short that will pull the ASU information) and the information will populate across the row? Same thing for cell A9 and Missouri.

Thank you,

Tim

 

[Smitty]

Sure, take a look at VLOOKUP.

HTH,

 

[Pezgordo]

Thanks for the info Smitty. I tried doing some VLOOKUP's but I keep getting #NA or #VALUE.

How do I place the team name in the first cell? Can I use "mo" as the lookup_value? Or do I need to use a40 (would this mean I would have to manually look up each team)?

Also can the column index number contain more than 1 column (I have 30+ columns in the worksheet)?

Here is what I tried: =VLOOKUP(A40,$B$6:$AJ$132,2:35,FALSE)

Any suggestions? As you can tell I am pretty much a rookie at this stuff.

 

[Sal Paradise]

<b>Excel 2003</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><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><th>K</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;;">Team</td><td style="font-weight: bold;;">Stat 1</td><td style="font-weight: bold;;">Stat 2</td><td style="font-weight: bold;;">Stat 3</td><td style="font-weight: bold;;">Stat 4</td><td style="font-weight: bold;;">Stat 5</td><td style="font-weight: bold;;">Stat 6</td><td style="font-weight: bold;;">Stat 7</td><td style="font-weight: bold;;">Stat 8</td><td style="font-weight: bold;;">Stat 9</td><td style="font-weight: bold;;">Stat 10</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style=";">Echo State</td><td style="text-align: right;;">61</td><td style="text-align: right;;">55</td><td style="text-align: right;;">8</td><td style="text-align: right;;">44</td><td style="text-align: right;;">13</td><td style="text-align: right;;">98</td><td style="text-align: right;;">43</td><td style="text-align: right;;">97</td><td style="text-align: right;;">77</td><td style="text-align: right;;">35</td></tr><tr ><td style="color: #161120;text-align: center;">3</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><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="font-weight: bold;;">Team</td><td style="font-weight: bold;;">Stat 1</td><td style="font-weight: bold;;">Stat 2</td><td style="font-weight: bold;;">Stat 3</td><td style="font-weight: bold;;">Stat 4</td><td style="font-weight: bold;;">Stat 5</td><td style="font-weight: bold;;">Stat 6</td><td style="font-weight: bold;;">Stat 7</td><td style="font-weight: bold;;">Stat 8</td><td style="font-weight: bold;;">Stat 9</td><td style="font-weight: bold;;">Stat 10</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style=";">Alpha State</td><td style="text-align: right;;">66</td><td style="text-align: right;;">61</td><td style="text-align: right;;">76</td><td style="text-align: right;;">73</td><td style="text-align: right;;">21</td><td style="text-align: right;;">48</td><td style="text-align: right;;">29</td><td style="text-align: right;;">5</td><td style="text-align: right;;">88</td><td style="text-align: right;;">51</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style=";">Bravo State</td><td style="text-align: right;;">74</td><td style="text-align: right;;">93</td><td style="text-align: right;;">10</td><td style="text-align: right;;">2</td><td style="text-align: right;;">27</td><td style="text-align: right;;">89</td><td style="text-align: right;;">82</td><td style="text-align: right;;">55</td><td style="text-align: right;;">62</td><td style="text-align: right;;">48</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">Charlie State</td><td style="text-align: right;;">51</td><td style="text-align: right;;">54</td><td style="text-align: right;;">97</td><td style="text-align: right;;">22</td><td style="text-align: right;;">60</td><td style="text-align: right;;">85</td><td style="text-align: right;;">65</td><td style="text-align: right;;">23</td><td style="text-align: right;;">56</td><td style="text-align: right;;">71</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style=";">Delta State</td><td style="text-align: right;;">23</td><td style="text-align: right;;">34</td><td style="text-align: right;;">78</td><td style="text-align: right;;">37</td><td style="text-align: right;;">2</td><td style="text-align: right;;">53</td><td style="text-align: right;;">99</td><td style="text-align: right;;">13</td><td style="text-align: right;;">98</td><td style="text-align: right;;">91</td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style=";">Echo State</td><td style="text-align: right;;">61</td><td style="text-align: right;;">55</td><td style="text-align: right;;">8</td><td style="text-align: right;;">44</td><td style="text-align: right;;">13</td><td style="text-align: right;;">98</td><td style="text-align: right;;">43</td><td style="text-align: right;;">97</td><td style="text-align: right;;">77</td><td style="text-align: right;;">35</td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style=";">Foxtrot State</td><td style="text-align: right;;">74</td><td style="text-align: right;;">69</td><td style="text-align: right;;">1</td><td style="text-align: right;;">81</td><td style="text-align: right;;">5</td><td style="text-align: right;;">95</td><td style="text-align: right;;">12</td><td style="text-align: right;;">88</td><td style="text-align: right;;">97</td><td style="text-align: right;;">31</td></tr><tr ><td style="color: #161120;text-align: center;">11</td><td style=";">Golf State</td><td style="text-align: right;;">76</td><td style="text-align: right;;">68</td><td style="text-align: right;;">74</td><td style="text-align: right;;">73</td><td style="text-align: right;;">6</td><td style="text-align: right;;">88</td><td style="text-align: right;;">0</td><td style="text-align: right;;">52</td><td style="text-align: right;;">46</td><td style="text-align: right;;">68</td></tr><tr ><td style="color: #161120;text-align: center;">12</td><td style=";">Hotel State</td><td style="text-align: right;;">13</td><td style="text-align: right;;">100</td><td style="text-align: right;;">11</td><td style="text-align: right;;">55</td><td style="text-align: right;;">51</td><td style="text-align: right;;">49</td><td style="text-align: right;;">26</td><td style="text-align: right;;">98</td><td style="text-align: right;;">26</td><td style="text-align: right;;">62</td></tr><tr ><td style="color: #161120;text-align: center;">13</td><td style=";">India State</td><td style="text-align: right;;">35</td><td style="text-align: right;;">52</td><td style="text-align: right;;">97</td><td style="text-align: right;;">70</td><td style="text-align: right;;">24</td><td style="text-align: right;;">30</td><td style="text-align: right;;">68</td><td style="text-align: right;;">8</td><td style="text-align: right;;">81</td><td style="text-align: right;;">88</td></tr><tr ><td style="color: #161120;text-align: center;">14</td><td style=";">Juliet State</td><td style="text-align: right;;">82</td><td style="text-align: right;;">6</td><td style="text-align: right;;">63</td><td style="text-align: right;;">12</td><td style="text-align: right;;">17</td><td style="text-align: right;;">83</td><td style="text-align: right;;">34</td><td style="text-align: right;;">82</td><td style="text-align: right;;">58</td><td style="text-align: right;;">86</td></tr><tr ><td style="color: #161120;text-align: center;">15</td><td style=";">Kilo State</td><td style="text-align: right;;">94</td><td style="text-align: right;;">75</td><td style="text-align: right;;">18</td><td style="text-align: right;;">22</td><td style="text-align: right;;">63</td><td style="text-align: right;;">83</td><td style="text-align: right;;">2</td><td style="text-align: right;;">26</td><td style="text-align: right;;">61</td><td style="text-align: right;;">82</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><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: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=INDEX(<font color="Blue">TeamData,MATCH(<font color="Red">$A2,TeamName,0</font>),COLUMN(<font color="Red"></font>)</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Workbook Defined Names</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Name</th><th style="text-align:left">Refers To</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">TeamData</th><td style="text-align:left">=Sheet1!$A$4:$K$15</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">TeamName</th><td style="text-align:left">=Sheet1!$A$4:$A$15</td></tr></tbody></table></td></tr></table><br />

TeamName is defined as A5:A15

TeamData is defined as A5:K15

(Use Insert -> Name to define names, or just don't use names)

I also created a dropdown in A2 using Data -> Data Validation, "List" as "TeamName" so that you can select the team name. The rest of the values will automatically be added using the formula in B2 copied across.

 

[Smitty]

Your problem is in the column reference:

=VLOOKUP(A40,$B$6:$AJ$132,2:35,FALSE)

It should be:

=VLOOKUP(A40,$B$6:$AJ$132,2,FALSE)

As for changing the column references you can make it dynamic by using COLUMN() instead of the static 2, 3, 4, etc. You'll find some working examples here.

With regards to the team name selection I'd use a Data Validation list. You'll find an article on how to do that through the same link.

 

[Pezgordo]

Thanks Sal, the formula worked perfectly. Smitty, thanks for explaining dynamic column(s), it was a great help.

 

[Pezgordo]

Excel 2003<table style="background-color: rgb(255, 255, 255); border: 1px solid rgb(166, 170, 182); border-collapse: collapse;" cellpadding="2.5px" rules="all"><colgroup><col style="background-color: rgb(224, 224, 240);" width="25px"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead><tr style="background-color: rgb(224, 224, 240); 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><th>K</th></tr></thead><tbody><tr><td style="color: rgb(22, 17, 32); text-align: center;">1</td><td style="font-weight: bold;">Team</td><td style="font-weight: bold;">Stat 1</td><td style="font-weight: bold;">Stat 2</td><td style="font-weight: bold;">Stat 3</td><td style="font-weight: bold;">Stat 4</td><td style="font-weight: bold;">Stat 5</td><td style="font-weight: bold;">Stat 6</td><td style="font-weight: bold;">Stat 7</td><td style="font-weight: bold;">Stat 8</td><td style="font-weight: bold;">Stat 9</td><td style="font-weight: bold;">Stat 10</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">2</td><td style="">Echo State</td><td style="text-align: right;">61</td><td style="text-align: right;">55</td><td style="text-align: right;">8</td><td style="text-align: right;">44</td><td style="text-align: right;">13</td><td style="text-align: right;">98</td><td style="text-align: right;">43</td><td style="text-align: right;">97</td><td style="text-align: right;">77</td><td style="text-align: right;">35</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">3</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><td style="text-align: right;">
</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">4</td><td style="font-weight: bold;">Team</td><td style="font-weight: bold;">Stat 1</td><td style="font-weight: bold;">Stat 2</td><td style="font-weight: bold;">Stat 3</td><td style="font-weight: bold;">Stat 4</td><td style="font-weight: bold;">Stat 5</td><td style="font-weight: bold;">Stat 6</td><td style="font-weight: bold;">Stat 7</td><td style="font-weight: bold;">Stat 8</td><td style="font-weight: bold;">Stat 9</td><td style="font-weight: bold;">Stat 10</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">5</td><td style="">Alpha State</td><td style="text-align: right;">66</td><td style="text-align: right;">61</td><td style="text-align: right;">76</td><td style="text-align: right;">73</td><td style="text-align: right;">21</td><td style="text-align: right;">48</td><td style="text-align: right;">29</td><td style="text-align: right;">5</td><td style="text-align: right;">88</td><td style="text-align: right;">51</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">6</td><td style="">Bravo State</td><td style="text-align: right;">74</td><td style="text-align: right;">93</td><td style="text-align: right;">10</td><td style="text-align: right;">2</td><td style="text-align: right;">27</td><td style="text-align: right;">89</td><td style="text-align: right;">82</td><td style="text-align: right;">55</td><td style="text-align: right;">62</td><td style="text-align: right;">48</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">7</td><td style="">Charlie State</td><td style="text-align: right;">51</td><td style="text-align: right;">54</td><td style="text-align: right;">97</td><td style="text-align: right;">22</td><td style="text-align: right;">60</td><td style="text-align: right;">85</td><td style="text-align: right;">65</td><td style="text-align: right;">23</td><td style="text-align: right;">56</td><td style="text-align: right;">71</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">8</td><td style="">Delta State</td><td style="text-align: right;">23</td><td style="text-align: right;">34</td><td style="text-align: right;">78</td><td style="text-align: right;">37</td><td style="text-align: right;">2</td><td style="text-align: right;">53</td><td style="text-align: right;">99</td><td style="text-align: right;">13</td><td style="text-align: right;">98</td><td style="text-align: right;">91</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">9</td><td style="">Echo State</td><td style="text-align: right;">61</td><td style="text-align: right;">55</td><td style="text-align: right;">8</td><td style="text-align: right;">44</td><td style="text-align: right;">13</td><td style="text-align: right;">98</td><td style="text-align: right;">43</td><td style="text-align: right;">97</td><td style="text-align: right;">77</td><td style="text-align: right;">35</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">10</td><td style="">Foxtrot State</td><td style="text-align: right;">74</td><td style="text-align: right;">69</td><td style="text-align: right;">1</td><td style="text-align: right;">81</td><td style="text-align: right;">5</td><td style="text-align: right;">95</td><td style="text-align: right;">12</td><td style="text-align: right;">88</td><td style="text-align: right;">97</td><td style="text-align: right;">31</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">11</td><td style="">Golf State</td><td style="text-align: right;">76</td><td style="text-align: right;">68</td><td style="text-align: right;">74</td><td style="text-align: right;">73</td><td style="text-align: right;">6</td><td style="text-align: right;">88</td><td style="text-align: right;">0</td><td style="text-align: right;">52</td><td style="text-align: right;">46</td><td style="text-align: right;">68</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">12</td><td style="">Hotel State</td><td style="text-align: right;">13</td><td style="text-align: right;">100</td><td style="text-align: right;">11</td><td style="text-align: right;">55</td><td style="text-align: right;">51</td><td style="text-align: right;">49</td><td style="text-align: right;">26</td><td style="text-align: right;">98</td><td style="text-align: right;">26</td><td style="text-align: right;">62</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">13</td><td style="">India State</td><td style="text-align: right;">35</td><td style="text-align: right;">52</td><td style="text-align: right;">97</td><td style="text-align: right;">70</td><td style="text-align: right;">24</td><td style="text-align: right;">30</td><td style="text-align: right;">68</td><td style="text-align: right;">8</td><td style="text-align: right;">81</td><td style="text-align: right;">88</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">14</td><td style="">Juliet State</td><td style="text-align: right;">82</td><td style="text-align: right;">6</td><td style="text-align: right;">63</td><td style="text-align: right;">12</td><td style="text-align: right;">17</td><td style="text-align: right;">83</td><td style="text-align: right;">34</td><td style="text-align: right;">82</td><td style="text-align: right;">58</td><td style="text-align: right;">86</td></tr><tr><td style="color: rgb(22, 17, 32); text-align: center;">15</td><td style="">Kilo State</td><td style="text-align: right;">94</td><td style="text-align: right;">75</td><td style="text-align: right;">18</td><td style="text-align: right;">22</td><td style="text-align: right;">63</td><td style="text-align: right;">83</td><td style="text-align: right;">2</td><td style="text-align: right;">26</td><td style="text-align: right;">61</td><td style="text-align: right;">82</td></tr></tbody></table>

Sheet1​

<table style="border: 2px solid black; border-collapse: collapse; padding: 0.4em; background-color: rgb(255, 255, 255);" width="85%" cellpadding="2.5px" rules="all"><tbody><tr><td style="padding: 6px;">Worksheet Formulas<table style="border: 1px solid rgb(166, 170, 182); text-align: center; background-color: rgb(255, 255, 255); border-collapse: collapse;" width="100%" cellpadding="2.5px" rules="all"><thead><tr style="background-color: rgb(224, 224, 240); 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 style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">B2</th><td style="text-align: left;">=INDEX(TeamData,MATCH($A2,TeamName,0),COLUMN())</td></tr></tbody></table></td></tr></tbody></table>
<table style="border: 2px solid black; border-collapse: collapse; padding: 0.4em; background-color: rgb(255, 255, 255);" width="85%" cellpadding="2.5px" rules="all"><tbody><tr><td style="padding: 6px;">Workbook Defined Names<table style="border: 1px solid rgb(166, 170, 182); text-align: center; background-color: rgb(255, 255, 255); border-collapse: collapse;" width="100%" cellpadding="2.5px" rules="all"><thead><tr style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);"><th width="10px">Name</th><th style="text-align: left;">Refers To</th></tr></thead><tbody><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">TeamData</th><td style="text-align: left;">=Sheet1!$A$4:$K$15</td></tr><tr><th style="background-color: rgb(224, 224, 240); color: rgb(22, 17, 32);" width="10px">TeamName</th><td style="text-align: left;">=Sheet1!$A$4:$A$15</td></tr></tbody></table></td></tr></tbody></table>


TeamName is defined as A5:A15

TeamData is defined as A5:K15

(Use Insert -> Name to define names, or just don't use names)

I also created a dropdown in A2 using Data -> Data Validation, "List" as "TeamName" so that you can select the team name. The rest of the values will automatically be added using the formula in B2 copied across.

Is it possible to use this formula (or a variation of it) if I have a worksheet that contains the same 120 teams, but instead of the team stats being in rows, they are in columns?

Using the example above, instead of Echo State starting at column A9 and there stats going (horizontally) out to K9. In this new worksheet Echo State would start at I1 and their stats would go down (vertically) to I11. I would than like to write "Echo State" in column B (cell B1) and have the stats appear below.

Thank you,

Tim

 

[Sal Paradise]

Change Team Name to be the top row, change Team Data to match where your data is, and change the formula:

=INDEX(TeamData,ROW(),MATCH(B$1,TeamName,0))

Index is simple.

=INDEX(where to look, what row, what column)

Match is simple.

=MATCH(what to look for, where to look, 0 [for exact match])

=ROW() gives you the current row.

=COLUMN() gives you the current column.

Combine as necessary for whatever way your data is arranged.

 

[Pezgordo]

Thanks again Sal. It works perfect. I was trying something similar, but kept putting Row in the wrong location.