Solved Drop down NFL Teams and VLookup?

February 26, 2017 at 16:53:22
Specs: Windows 10
Greetings. I currently have 2 drop down lists with all 32 NFL teams. One labeled Visiting team, and the other home team. Here is what I need to happen: When I select, say, Arizona, I need to pull certain rushing and passing stats into this sheet so as to make a prediction on the game outcome. I've been told VLookup will do this, but cannot put it together in my head. Thank you in advance for your help with this!

See More: Drop down NFL Teams and VLookup?

Report •

✔ Best Answer
March 1, 2017 at 09:59:37
Hopefully you've done your homework on Named Ranges because that is part of the solution that I am proposing. We are also going to use the INDEX and INDIRECT functions.

Let's break these items down first, then combine them as a solution…

Named Ranges

The Named Range feature allows you to assign a name to a cell or range of cells that tells you something about the cell and/or range. For example, if I have some data related to Thurman in A1, I can name that cell "Thurman" and use Thurman in a formula to reference A1. I can use =Thurman or =Thurman+1, etc. Excel will use the value in A1 since Thurman refers to A1.

If I name a range of cells, e.g. B5:C14, I can then use that name in any formula that requires a range as an argument. For example, if I name B5:C14 "Running_Backs", I could use something like =INDEX(Running_Backs,5,2) which would be equivalent to =INDEX(B5:C14,5,2) since Running_Backs refers to B5:C14.

INDEX function

=INDEX(table, row_number, column_number )

The INDEX function will pull data from a table (or range) based on the row and column number that you provide. The key thing to remember with the INDEX function is that the row_number and column_number arguments refer to the row number and column number of the table, not the spreadsheet. e.g.

=INDEX(B5:C14,5,2) would pull data from C9 since C9 is in the 5th row and 2nd column of range B5:C14.

INDIRECT function

=INDIRECT(ref_text)

The INDIRECT function accepts a text string and converts it to a cell reference.

For example, let’s say I had data in D1:D5. If I want data from D2, I could simply use =D2. But let’s say I know I want data from Column D, but the Row number will be based on the result of another formula, e.g. SUM(A1:A3). I could use:

=INDIRECT("D"&SUM(A1:A3))

If SUM(A1:A3) = 2, then the formula would reference D2.

If =SUM(A1:A3) were in a another cell, say F1, I could use:

=INDIRECT("D"&F1)

Whatever number appeared in F1 would be appended to “D” as the row number and the formula would reference that cell.

OK, now let’s put this all together.

I’m going to give you suggested formula for E10 on your Analyzer sheet (Visiting Team - Rush Attempts) and then explain how it works. I’ll leave to you to write all the other formulas based on that explanation.

Put this formula in E10 and expect to get a #REF! error. That will be taken care of shortly:

=INDEX(INDIRECT(E7),8,1)

Here’s what you need to do to eliminate the #REF! error and pull an actual piece of data into that cell:

1 – On the Main sheet, select U3:U23. You are about to Name that range.

2 – Above Column A you should see a box showing U3 and drop down arrow. Click inside that box which should result in the “U3” text being selected. Replace U3 with the word Arizona and press Enter. You must press Enter in order for the Name to take. If you click outside of that box before hitting Enter, the Name will not be applied.

3 – As a test, click any cell you want, then click the down arrow in that box and choose Arizona. U3:U23 should be selected.

4 – Put a value in Main!C10, e.g. 45, so that you get a value in Main!U10 next to Avg Rush Attempts – STD

5 – On the Analyzer sheet, if Arizona is already selected in the Visiting Team drop down, you should see the value from Main!U10 in Analyzer!E10. e.g. 45. If Arizona is not selected, you’ll still have the error, so choose Arizona in the drop down and see if it works.

What should have happened is that the INDIRECT function used the value from Analyzer!E7 (Arizona) and converted it to a reference to the range named Arizona, which refers to Main! U3:U23.

INDIRECT(E7) is equivalent to INDIRECT("Arizona") is equivalent to Main!U3:U23

The INDEX function then grabbed the value in the 8th row and 1st column of that range, which happens to be Main!U10.

OK, assuming that worked, here is how to complete your task:

6 – On the Main sheet, you now need to create Named Ranges for every team just like you did for Arizona. U25:U45 will be named Atlanta, U47:U67 will be named Baltimore, etc. As long as the table for each team is laid out exactly the same, the INDEX function, which uses “8” as the row_number argument, will always pull the Avg Rush Attempts – STD from the current Named Range (the Analyzer!E7 value) into Analyzer!E10.

7 – For the Home Team section of the Analyzer page, the formula for I10 will be:

=INDEX(INDIRECT(I7),8,1) because you want to reference the team name showing in the Home Team drop down.

8 – For all your other cells on the Analyzer sheet (F10, E11, F11, etc.) You’ll need to change the row_number argument of the INDEX function in order to pull the data from the correct Row of the Team table on the Main sheet. The column_number argument will always be 1, since the table is only 1 column wide. Just keep in mind that you want the row number of the table not the sheet. As I said earlier, using “8” as the row_number argument will pull the Avg Rush Attempts – STD value from any table as long as the Avg Rush Attempts – STD value is always in the 8th row of every team’s table.

That’s it. Get the row_number right for each stat that you want to pull and make sure you reference the correct drop down (Visiting Team vs. Home Team) and you should be all set. Feel free to come back with any questions that you have.

BTW…you may be wondering why VLOOKUP won’t work. VLOOKUP needs to find a lookup value in the source table. Since you use Rush Attempts on the Analyzer sheet and Avg Rush Attempts – STD on the Main sheet, you don’t have a matching value for VLOOKUP to find. If you wanted to rewrite your sheets so that the text in Column B of the Analyzer sheet matched the text on the Main sheet for each stat, we could use VLOOKUP, but you’d still need the Named Ranges and INDIRECT functions in order to pull data from the correct team table. Using INDEX works just as well as VLOOKUP.

Let us know how this works for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.



#1
February 26, 2017 at 18:16:43
Without knowing how your sheet is laid out, it's hard to offer anything more than a generic suggestion. For example, let's say your sheet looks like this, with your drop down in A2 and your stats table in E1:G3. (Obviously it will be much longer and wider)

        A      B         C        D    E       F          G
1     Team    Rushing   Passing      Team    Rushing    Passing
2    Buffalo                        Arizona    300        200
3		                    Buffalo    400        450

Now let's say you want to pull the Rushing and Passing stats for your A2 choice into B2 & C2.

Try these formulas:

B2: =VLOOKUP(A2,$E$2:$G$3,2,0) (Should return 400)
C2: =VLOOKUP(A2,$E$2:$G$3,3,0) (Should return 450)

I don't know if you are familiar with how VLOOKUP works, so I'll save the explanation unless you ask. I'm more than willing to explain it, but I don't want to waste my time or yours if the explanation is not needed. :-)

If your spreadsheet is much more complicated than I'm guessing, you'll need to explain the setup and explain why you are having trouble with VLOOKUP.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
February 26, 2017 at 20:20:29
Thank you for your help with this. I'll try to lay this out in the format that I currently have;

Visiting Team Home Team
(Drop Down Box) (Drop Down Box)

Visitor Home Home Vistor
Offense Defense Offense Defense

Rush Att's (Cell) (Cell) (Cell) (Cell)
Rush Yds (Cell) (Cell) (Cell) (Cell)

This is the format for rushing figures. Right below this would be the same format only for passing stats. So, What I'm trying to accomplish is when a certain pair of teams is selected from the drop down list, those stats for those particular teams need to populate the cells pictured so a score prediction can be made. Let me know if this helps, if not, I would be willing to take a screen snap shot if you let me know how to accomplish this in windows 10. And thanks again for your interest in my program.


Report •

#3
February 27, 2017 at 05:49:33
I am not able to determine how your data is laid out.

Please click on the blue line at the end of this post and read the instructions on how to format example data so that it is easier for us to read. Then edit/repost your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example.

Thanks!

You can't post screenshots or attach files in this forum. The standard practice is to post the screenshot or file at a image sharing or file sharing site and then post the link back here in the forum. imgur is a decent image sharing site and zippyshare is a decent file sharing site. Neither of those will require us to register in order to download your image or file. I, for one, prefer not to have to register at a site just to be able to download a file in order to answer a question in the forum. Many others feel the same way.

Obviously, a file that we can actually work with is better than a screen shot, but if you feel that a screen shot will provide the information we need, feel free to try that.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
February 27, 2017 at 06:52:55
Visiting Team                    Home Team


Report •

#5
February 27, 2017 at 07:05:52
You might want to try that again. All I see is one line. That's not a lot for us to work with.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
February 27, 2017 at 13:04:52
Sorry about that, am having a problem getting the picture to you as I don't see a way to post it to you on here.

Report •

#7
February 27, 2017 at 13:21:52
Why don't you do what I suggested in response # 3?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#8
February 27, 2017 at 19:13:51
I tried that, but didn't know where to post the link to the picture so you could see it. It's already on zippyshare, but just need to know how to post the link as the blue link information appears vague. Sorry.

Report •

#9
February 27, 2017 at 19:59:34

Report •

#10
February 27, 2017 at 20:29:05
Duh.....sorry about that. I'm not normally this stupid I promise....

Here you go. http://www101.zippyshare.com/v/ZWWh...

This the sheet where the predictions will take place. Pulling data from another sheet called "Main". If you need to see this sheet as well, I would be glad to post a link to this one as well. Thanks for all your help as well..


Report •

#11
February 27, 2017 at 21:04:25
There is not much that I can do with just that image. I need to know where the data is coming from and how it is laid out.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#12
February 28, 2017 at 06:10:59
I would also like to know if you understand how the VLOOKUP function works.

At this point, I'm not saying that VLOOKUP is the method you should use. I won't know that until I see how your source data is laid out, but assuming that VLOOKUP will work, I would like to know how much of an explanation I should provide to make sure that you can adapt it to your needs.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#13
February 28, 2017 at 06:51:00
I know a little bit about it from following some instructional videos, and I think I get the concept of how it works, but just can't seem to understand how to program the same cells to pull data over from a different worksheet for all 32 teams. That's where the problem rests for me.

Report •

#14
February 28, 2017 at 06:55:27
Are you going to post a link to the source data? I can't do anything until I know how the source data is laid out. I've asked for the layout of the source numerous times.Until you provide that information, there is nothing else that I can do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#15
February 28, 2017 at 07:06:32
My Apologies as I didn't see the second request. Anyway, here is the picture of what the sheet looks like for Arizona. There are 31 more laid out just like this with data needing to be pulled over from the right side of the sheet before the 2016 averages as those will be 2017 season to date averages as the scores and stats get entered from pro-football-reference.com.

http://www82.zippyshare.com/v/Dy4I3...


Report •

#16
February 28, 2017 at 07:53:10
re: " Anyway, here is the picture of what the sheet looks like for Arizona.

Your image does not include any Column letters or Row numbers. Without that information the best I can offer is a generic solution. I would rather not make assumptions and guesses as to the complete layout of your workbook, so it would be best if you did one of 2 things:

Either repost both of your images and include the Column letters and Row numbers or upload the entire file to zippyshare and post a link here.

I would prefer to have access to the entire file so that I do not have to recreate the file from an image. With the actual workbok in front of me, I can test various options. If you do decide to post the entire file, please put some example source data in the team tables. i.e. Make believe that it is the 3rd week of the season and that you already have data for weeks 1 & 2. That way we have some actual data to pull into the destination table.

re: "There are 31 more laid out just like this""

I don't know what that means. 31 more tables on a single sheet or 31 more sheets with a single team's table on each sheet?

That is another reason that I would prefer that you upload the entire workbook to zippyshare. I really want to see exactly what you have, what formulas you are using, what cells are merged, etc. If you want to condense it down to just 3 or 4 teams, that's fine. As long as I have enough source data to work with, I should be able to come up with a solution. At this point I'm still flying somewhat blind.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#17
February 28, 2017 at 20:15:18
Well, it sounds like I should have done this all along, but being as frustrating as this whole project has been for the past 2 years, I had all but given up. Anyway here is the entire workbook that I have been toying with. And I'm now of the opinion that if you can't fix this, nobody can apparently.

http://www52.zippyshare.com/v/GjIDd...

Again, sorry to make this so frustrating, and thank you for your help...


Report •

#18
February 28, 2017 at 20:25:48
I'll work on it tomorrow. Based on your screen shots I did play around with a few things and may have some ideas. In the meantime, if you are not familiar with Named Ranges, your should study up on them. I think that using Named Ranges will make a solution fairly simple.

If we name each team's stat table with the name of the team, we can then use the name from the drop down to reference the range for that team. We'll see.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#19
February 28, 2017 at 20:35:04
Hey, if it sounds good to you, then so be it. I'm open for just about anything that sounds like a solution after all this time... thanks again...

Report •

#20
March 1, 2017 at 09:59:37
✔ Best Answer
Hopefully you've done your homework on Named Ranges because that is part of the solution that I am proposing. We are also going to use the INDEX and INDIRECT functions.

Let's break these items down first, then combine them as a solution…

Named Ranges

The Named Range feature allows you to assign a name to a cell or range of cells that tells you something about the cell and/or range. For example, if I have some data related to Thurman in A1, I can name that cell "Thurman" and use Thurman in a formula to reference A1. I can use =Thurman or =Thurman+1, etc. Excel will use the value in A1 since Thurman refers to A1.

If I name a range of cells, e.g. B5:C14, I can then use that name in any formula that requires a range as an argument. For example, if I name B5:C14 "Running_Backs", I could use something like =INDEX(Running_Backs,5,2) which would be equivalent to =INDEX(B5:C14,5,2) since Running_Backs refers to B5:C14.

INDEX function

=INDEX(table, row_number, column_number )

The INDEX function will pull data from a table (or range) based on the row and column number that you provide. The key thing to remember with the INDEX function is that the row_number and column_number arguments refer to the row number and column number of the table, not the spreadsheet. e.g.

=INDEX(B5:C14,5,2) would pull data from C9 since C9 is in the 5th row and 2nd column of range B5:C14.

INDIRECT function

=INDIRECT(ref_text)

The INDIRECT function accepts a text string and converts it to a cell reference.

For example, let’s say I had data in D1:D5. If I want data from D2, I could simply use =D2. But let’s say I know I want data from Column D, but the Row number will be based on the result of another formula, e.g. SUM(A1:A3). I could use:

=INDIRECT("D"&SUM(A1:A3))

If SUM(A1:A3) = 2, then the formula would reference D2.

If =SUM(A1:A3) were in a another cell, say F1, I could use:

=INDIRECT("D"&F1)

Whatever number appeared in F1 would be appended to “D” as the row number and the formula would reference that cell.

OK, now let’s put this all together.

I’m going to give you suggested formula for E10 on your Analyzer sheet (Visiting Team - Rush Attempts) and then explain how it works. I’ll leave to you to write all the other formulas based on that explanation.

Put this formula in E10 and expect to get a #REF! error. That will be taken care of shortly:

=INDEX(INDIRECT(E7),8,1)

Here’s what you need to do to eliminate the #REF! error and pull an actual piece of data into that cell:

1 – On the Main sheet, select U3:U23. You are about to Name that range.

2 – Above Column A you should see a box showing U3 and drop down arrow. Click inside that box which should result in the “U3” text being selected. Replace U3 with the word Arizona and press Enter. You must press Enter in order for the Name to take. If you click outside of that box before hitting Enter, the Name will not be applied.

3 – As a test, click any cell you want, then click the down arrow in that box and choose Arizona. U3:U23 should be selected.

4 – Put a value in Main!C10, e.g. 45, so that you get a value in Main!U10 next to Avg Rush Attempts – STD

5 – On the Analyzer sheet, if Arizona is already selected in the Visiting Team drop down, you should see the value from Main!U10 in Analyzer!E10. e.g. 45. If Arizona is not selected, you’ll still have the error, so choose Arizona in the drop down and see if it works.

What should have happened is that the INDIRECT function used the value from Analyzer!E7 (Arizona) and converted it to a reference to the range named Arizona, which refers to Main! U3:U23.

INDIRECT(E7) is equivalent to INDIRECT("Arizona") is equivalent to Main!U3:U23

The INDEX function then grabbed the value in the 8th row and 1st column of that range, which happens to be Main!U10.

OK, assuming that worked, here is how to complete your task:

6 – On the Main sheet, you now need to create Named Ranges for every team just like you did for Arizona. U25:U45 will be named Atlanta, U47:U67 will be named Baltimore, etc. As long as the table for each team is laid out exactly the same, the INDEX function, which uses “8” as the row_number argument, will always pull the Avg Rush Attempts – STD from the current Named Range (the Analyzer!E7 value) into Analyzer!E10.

7 – For the Home Team section of the Analyzer page, the formula for I10 will be:

=INDEX(INDIRECT(I7),8,1) because you want to reference the team name showing in the Home Team drop down.

8 – For all your other cells on the Analyzer sheet (F10, E11, F11, etc.) You’ll need to change the row_number argument of the INDEX function in order to pull the data from the correct Row of the Team table on the Main sheet. The column_number argument will always be 1, since the table is only 1 column wide. Just keep in mind that you want the row number of the table not the sheet. As I said earlier, using “8” as the row_number argument will pull the Avg Rush Attempts – STD value from any table as long as the Avg Rush Attempts – STD value is always in the 8th row of every team’s table.

That’s it. Get the row_number right for each stat that you want to pull and make sure you reference the correct drop down (Visiting Team vs. Home Team) and you should be all set. Feel free to come back with any questions that you have.

BTW…you may be wondering why VLOOKUP won’t work. VLOOKUP needs to find a lookup value in the source table. Since you use Rush Attempts on the Analyzer sheet and Avg Rush Attempts – STD on the Main sheet, you don’t have a matching value for VLOOKUP to find. If you wanted to rewrite your sheets so that the text in Column B of the Analyzer sheet matched the text on the Main sheet for each stat, we could use VLOOKUP, but you’d still need the Named Ranges and INDIRECT functions in order to pull data from the correct team table. Using INDEX works just as well as VLOOKUP.

Let us know how this works for you.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#21
March 1, 2017 at 13:19:22
Outstanding! Will look at this closer when I get home from work tonight. And thank you once again for your efforts with this as I will definitely try what you have suggested.

Report •

#22
March 3, 2017 at 13:35:36
DerbyDay. I followed your instructions to the letter, and it works like a charm! I can't thank you enough for all that you did on this, as this has been an on-going project for over 2 years now. But most of all, thank you for being such a good teacher putting the instructions into words that people such as myself can understand and apply. You are a truly gifted teacher, and thank you once again....

Steve


Report •

#23
March 3, 2017 at 16:24:32
I'm glad you got it working. I do have a question for you:

One of the stats you have the Analyzer sheets is "Rushing Attempts Allowed". That is not a stat I am familiar with. Is that nothing more than the avg Rushing Attempts of that week's opponent?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#24
March 3, 2017 at 19:40:37
With each team playing each other differently as the weeks go by, that stat tells me how many rushing attempts their defense is allowing on a week to week basis, along with an average as well. Probably doesn't do anything for figuring point differentials, just shows the user how many times the other team ran the ball against their defense as opposed to passing.

Report •

#25
March 4, 2017 at 05:11:46
That's what I thought. So it's more of a rushing attempts against stat vs. allowed.

That might tell you something about their run defense, i.e. whether or not teams like to run against them.

Thanks.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#26
March 4, 2017 at 08:56:10
Correct. Sort of a visual thing for the users that see things that way. Same way with the charts, strictly visual. I did come across another question if you're up for it.
Some time ago, I was toying with the idea of trying to make the "Main" sheet more automated as far as how it gets it's data from say, another website. i.e. pro-football-reference.com. Is it possible to ask the program to only download certain stats, or does it have to get an entire section with formula's sorting out the data?

Report •

#27
March 6, 2017 at 07:44:12
I am not very familiar with downloading data from a website into Excel. It's just not something I need to do very often. When I do, I typically copy/paste and the move stuff around, or download a csv files and massage the data.

However, it doesn't necessarily have to be done with formulas. It's possible that a Macro might be more efficient - and less risky. A macro could pull actual values into the cells
eliminating the danger of a formula being changed or deleted.

Before I could offer any advice, I would need to know your current process and have access to the workbook that uses the formulas.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#28
March 6, 2017 at 12:17:28
Thank you for the input, as it was an idea I was just toying with. It does look and sound like a lot of work though. I think I'm going to "Massage" what I already have thanks to the awesome help that you have already provided with the named ranges. I can't begin to tell you how helpful that has been as I am now able to move forward with the rest of this project.

Report •

#29
March 10, 2017 at 09:11:15
DerbyDad, I came across another situation with my project needing help on one of the formulas. I'm hoping that you still have the spreadsheet that I sent you? If you don't I can send it again when I get home from work..

Steve


Report •

#30
Report •

#31
March 10, 2017 at 11:47:23
Well, on the "Main" sheet, on the right side where the season-to-date stats are, you'll see a couple of rows, one named ATS w/l/p, something like that, the other one is labeled
O/U w/l/p, those formulas in those cells are for all of the games a team will play. What I am trying to do is break the countif formula to show home games in that first column, then the away games in the column just to the right of that which I have set up. Is this even doable?

Report •

#32
March 10, 2017 at 13:17:58
For your Home Wins try this:

=SUMPRODUCT(--($C$6:$S$6="W"),(--(LEFT($C$2:$S$2,1)<>"@")))

For your Away Wins, try this:

=SUMPRODUCT(--($C$6:$S$6="W"),(--(LEFT($C$2:$S$2,1)="@")))

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#33
March 10, 2017 at 13:33:58
Looks good, what do I use for when the team doesn't cover the spread or over/under for that game? Is that what the <> @ part of the formula is for? Did you also see the existing formula that is there now? Does that make sense?

Report •

#34
March 10, 2017 at 14:32:10
The only thing I addressed was the W/L/T records for Home vs. Away, providing the formula for Wins and leaving the rest up to you. I provide the concept, you flesh it out to meet your needs.

You should read up on the SUMPRODUCT function.

If you put the following SUMPRODUCT suggestion in place of your IFERROR(COUNTIFS()) formula it will provide the number of wins for Away games. You'll need to append the sections for the Losses and Tie, but the format is basically the same as what you are doing now, repeating the function, but changing the criteria.

=SUMPRODUCT(--($C$6:$S$6="W"),(--(LEFT($C$2:$S$2,1)="@")))

Put this formula in a cell and then put some W's in the Game Outcome row for Arizona. Then use the Evaluate Formula feature on the Formulas ribbon to "Evaluate" the formula and watch it work step by step.

Basically what it does is return 2 arrays of 1's and 0's. The first array will show a 1 for any cell in C6:S6 that contains a W. The second array will show a 1 for any cell in C2:S2 where the first character is your Away symbol (@). (LEFT($C$2:$S$2,1)="@")

Once those arrays have been built, the function will "SUM" (actually it sort of counts) all positions where there is a 1 for a W and a 1 for the @ symbol. That is how it counts the number of wins for the Away games.

For the formula with the <>@, the second array will contain a 1 for each cell in C2:S2 that does not start with @. Those are your Home games.

Add similar clauses for Ties, Losses and Pushes, just like you did with your COUNTIFS and you should have the results you need.

Start small like I suggest so you can see how it works, then expand the formulas to included all of your criteria.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Ask Question