EXCEL-ING AT FANTASY BASEBALL: PART 3 – Lookups

Welcome to part 3 of Excel-ing at Fantasy Baseball. If you haven’t read the first and second parts yet, I’d recommend you start there (part 1 and part 2), so you can see the material we’ve covered (and maybe even go back to the other article I wrote that showed some of the benefits of Excel), to see how powerful and useful a tool Excel can be in Fantasy Baseball. It is also vital as we move on in this series that you understand the fundamentals of the previous articles. Finally, we began to build a spreadsheet over the last few weeks that we will continue to build this week, so skip parts one and two at your own risk.

What?! Your fantasy baseball league is not using Fantrax? Inconceivable! Check out everything Fantrax has to offer, and I’m sure you’ll come around to our way of thinking.

Using Excel to Win at Fantasy Baseball

Review/Recap

Week 1 we covered:

• the basics of formulas
• cell references and how to lock a cell reference
• some basic formulas such as:
  • sum
  • average
  • count
• and how to download and import data into a sheet

Week 2 we covered:

• Tables
  • How cell references work
  • How to name a table
  • The ease of copying formulas within tables

And today we will explore ways to lookup data from other tables and pull it into the same table, covering vlookup, index match, and my personal favorite xlookup.

Lookups

This is one of the most useful tools in Excel (or other spreadsheets) that exists. During a job I had in college at a company I got handed a project of transferring data from one sheet to another that someone had already spent 3 hours on doing manually…. I finished the project in 2 minutes with a single vlookup.

While xlookup is my personal favorite, it is also in Excel only and will break if you upload an Excel file into Google Sheets so I will also cover the other ones – we’ll start with Vlookup.

To prepare for this open up the spreadsheet you’ve built over the past few weeks. Your ranks sheet top row should look like this with your league name in there instead of 30 Rock (including maybe some other leagues if you added in more leagues).

First, before we do the lookups – add in Eric’s new top 300 prospects list  to a sheet named Cross 300 dynasty prospects and name the table Cross_prospects. Finally, add another tab on the Ranks sheet and type Cross_prospects. (That process is covered in part 1 and 2).

Also, at this point let’s confirm that your tables’ names are the same as the headers (we’ll get to indirect functions later but it might help then). It should look something like this:

Now we’ll do the lookups – we’ll do a vlookup in the Cross500 column, an index matchup in the Clegg500 column, and a xlookup in the Cross_prospects column. We’ll also go over some related helpful formulas like “iferror.”

VLOOKUP

Whenever you’re learning a new tool start by clicking on the Fx in the formula bar and in the formula builder type in the formula, in this case “VLOOKUP.” Click on it and you will this:

This is the syntax:

“VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

▪   Lookup_value: is the value to be found in the first column of the table, and can be a value, a reference, or a text string.

▪   Table_array: is a table of text, numbers, or logical values, in which data is retrieved. Table_array can be a reference to a range or a range name.

▪   Col_index_num: is the column number in table_array from which the matching value should be returned. The first column of values in the table is column 1.

▪          Range_lookup: is a logical value: to find the closest match in the first column (sorted in ascending order) = TRUE or omitted; find an exact match = FALSE.”

So, the lookup_value will be the value on the sheet, in our case the rank sheet, the table array will be the table we are looking it up in (in this case the Cross table), col_index_num is the number of the column there, and range lookup is if you are looking between certain data points or not. For most of our uses, this will be false since we are looking up a specific player.

An important note for vlookup is that the first column in your table MUST be the data you are looking up which is a large reason I prefer xlookup. So, if you check the Cross table you’ll notice that the ranks are in column A and we need to lookup the names in column B. So, to make that easier we’ll add a column called Rank 2 and in g2 type = and click a2 so it should read =[@RANK].

Now that we have done that, we can do the vlookup in the Cross column. So go back to the Ranks sheet and in the Cross500 column in the first row type =vlookup (then click on a2 and it should fill with [@Players], then type a comma and go to the Cross500 sheet and highlight all cells from the Player column to rank too, it should look like this: Cross500[[PLAYER]:[Rank2]]. Then type another comma, 6, because Rank2 is the 6^th column in that selected range, and then comma false for the range lookup. Complete it will look like this:

=VLOOKUP([@Players],Cross500[[PLAYER]:[Rank2]],6,FALSE)

Now, you’ll notice some of the cells have a #N/A value. This means that that there is an error in the player’s rank isn’t showing. In this case it simply means that the player is not ranked so the name can’t be found. In some future sheets when the rankings you pull in aren’t from Fantrax the error could be caused by other reasons. To clean that up visually you have some options using an iferror. An iferror function simply says if the first part of the function returns an error (such as #N/A) it will return the other value. (Of note this doesn’t fix the errors, it just makes it look cleaner).

If we type the “iferror” in around that formula it looks like: =IFERROR(VLOOKUP([@Players],Cross500[[PLAYER]:[Rank2]],6,FALSE),””). With this, the #N/A will disappear, and it will just show “”. Then you can go up to the top of the Cross cell and click the arrow. A window will appear like this:

If you hit descending it will rank the table 1-500.

Index – match

Index match is a two formula route to look up information so we’ll introduce that one in the Clegg500 column. Keaton DeRocher, the content manager at @dynastyguru, describes index match as “Love of my life ❤️❤️❤️❤️💕” so I guess it’s worth covering.

Index is a formula that, at its basis, returns a value at a given range in a location so, to use this picture example from exceljet,

This tells the formula the range the data is in. You can then also provide the row – again from exceljet:

That’s the basics on index.

Match then finds the position of a certain thing within a range, and returning the value – once more courtesy of exceljet:

So – when we combine these two we can find the location of a value in the table with match, and then the value of that in another column so to go back to our ranks sheet.

Given all that – in the Clegg500 column this is the formula:

=INDEX(Clegg500[RANK],MATCH([@Players],Clegg500[PLAYER],0))

Clegg500[Rank] is the rank column in the Clegg500 sheet, and then in the Match formula it’s the Players column on the rank sheet, the player column on the Clegg ranking, and a 0 because it’s an exact match.

We’ll now add in the same ifferror as above to have a total formula of: =IFERROR(INDEX(Clegg500[RANK],MATCH([@Players],Clegg500[PLAYER],0)),””)

Seventh Inning Stretch:

We’ll take a break from lookups before doing xlookup and add in an average column. Just to the right of names add a column and name it average, then in b2 type in =average( and select the first two cells of c2 and d2 (Cross and Clegg) and =AVERAGE(Ranking[@[Cross500]:[Clegg500]]). You will again see an error where there isn’t data to average, so add the same sort of iferror formula there as well and then sort the average column. Your end result should look like this:

XLOOKUP

Now for my favorite – xlookup.

If you look at the excel information you will see this:

“XLOOKUP(lookup_value,lookup_array,return_array,if_not_found,match_mode,search_mode)

▪   Lookup_value: is the value to search for.

▪   Lookup_array: is the array or range to search.

▪   Return_array: is the array or range to return.

▪   If_not_found: returned if no match is found.

▪   Match_mode: specify how to match lookup_value against the values in lookup_array.

▪   Search_mode: specify the search mode to use. By default, a first to last search will be used.”

As you might expect – this is all pretty straightforward.

Let’s dive right in. In Cross_Prospects in the first row type =xlookup( and then click on the first cell in the player column and type a comma, then go to the top 300 prospects sheet and click on the top row and cmd+shift+down arrow to select the whole column (reminder if on a non-Mac it’s ctrl not cmd). Once you have done that, type another comma and do the same thing in the rank column. Then type , “” for the if_not_found value – which will do what we have done in the iferror formulas before. Then ,0,1 for exact match and search top to bottom. The formula will look like this:

=XLOOKUP([@Players],Cross_Prospects[PLAYER],Cross_Prospects[RANK],””,0,1).

Now – you might notice two “2” values next to each other and see two Wander Francos! That’s because in the downloaded Fantrax player pool there are multiple Wander’s. We’ll get into how to fix that later.

Final touches

(This time)

Now in the age column do whatever form of those you prefer to find the age of each player. We’ll use the player pool you downloaded on one of your team pages. I’m using the D1S 30 Rock league.

I’ll do xlookup – which looks like this:

“=XLOOKUP([@Players],ThirtyRock[Player],ThirtyRock[Age],””,0,1)”

Now copy and paste that formula into the position column and adjust the formula as needed. For xlookup that just means changing the age column to the position column which you can do as easily as going into the formula and typing in ‘position’ where it says age.

Then do this again for your league(s) columns and return the “status” – ie who rosters that player (if you have multiple league player pools downloaded you can do it for each table).

So my sheet now looks like this for the top 35 players on average between Eric’s top 500 and Chris’ top 500 OBP dynasty ranks (before you assume average rankings answer all your questions and trade approaches please go back and read this.)

That’s where we will leave it for this week – we’ll come back next week and work on some more skills to pull in data from other resources and build data for each league.

Happy Excel-ing!

Fantrax was one of the fastest-growing fantasy sites of 2020, and we’re not stopping now. With multi-team trades, designated commissioner/league managers, and drag/drop easy click methods, Fantrax is sure to excite the serious fantasy sports fan – sign up now for a free year at Fantrax.com.