Forum Discussion
Ignore Row with Text Using VLOOKUP
I am building an NBA stat tracking model. As a part of it I am pulling data from each player's Basketball Reference page (each player has their own sheet that is getting data through a query) for the example I am linking to, the player's name is Saddiq Bey (sheet name Bey_Saddiq). Based on that data, I have a separate sheet (sheet name is Points) that auto-updates the value of a stat (Points in this example) in the previous 15 games with Column C representing the most recent game, Column D the second most recent game, etc.
The final sheet (sheet name is PointsOU) has the current vegas line for the specific stat of that player and counts how many times that player has gone over or under that number (Columns E and H have the stat line from two different books). Beginning at column R I have columns for the last 3 games, last 5 games, last 10 games, and last 15 games. The main issue I am having is that as the basketball reference page for a player gets longer and longer the data query sheet will occasionally duplicate the column headers. So when I try to pull the stat number for a cell that is the column header I get text (PTS) instead of a number value. Then, when I try to count the number of times a player has been over or under a value and the text is in one of cells that I am counting the entire formula returns a blank.
Any idea of how to skip the cells with PTS? Ideally, if I was checking the previous 3, 5, 10, or 15 games and PTS was in one of the cells, then the formula would actually SKIP this cell and count an additional cell (since the PTS is actually not a game).
Here is a sample sheet: https://docs.google.com/spreadsheets/d/1Ya5H4fPfdwwiUGEppRNJVjO1fI-TQ_Ds/edit?usp=sharing&ouid=107459650418333218348&rtpof=true&sd=true
Thank you!
To skip the cells with PTS, you can use the IF function to check if the cell contains text. If it does, you can return an empty string. Otherwise, you can return the value of the cell.
Here is an example of how to use the IF function to skip the cells with PTS:
=IF(ISTEXT(cell), "", VLOOKUP(cell, lookup_table, col_index_num, range_lookup))
In this formula, the cell parameter is the cell that you want to check. The lookup_table parameter is the table that you want to search. The col_index_num parameter is the column number in the lookup table that contains the value that you want to find. The range_lookup parameter is a logical value that determines whether to search for an exact match or an approximate match.
Here is an example of how to use the formula to count the number of times that a player has been over or under a value in the last 3 games:
=SUMIF(A5:A27, E2, IF(ISTEXT(A5:A27), "", VLOOKUP(A5:A27, Points, 2, FALSE))) + SUMIF(A5:A27, H2, IF(ISTEXT(A5:A27), "", VLOOKUP(A5:A27, Points, 2, FALSE)))
In this formula, the A5:A27 range contains the last 3 games. The E2 and H2 cells contain the vegas line for the specific stat of the player. The Points sheet contains the previous 15 games of data.
This formula will count the number of times that the player has gone over or under the vegas line in the last 3 games. If a cell in the A5:A27 range contains text, then the formula will ignore that cell and count an additional cell. The text and steps were edited with the help of AI.
My answers are voluntary and without guarantee!
Hope this will help you.
Was the answer useful? Mark as best response and Like it!
This will help all forum participants.
- NikolinoDE thank you for your response!
I am confused about the final formula. Can you clarify what you mean by A5:A27 is referencing the last three games? Which sheet were you referencing? Because new rows are added with each new game, I have it set up to pull the values for those games across a row for each player (Points sheet, rows C-Q).
Also, with VLOOKUP wouldn't you want to have the first argument of the formula to be a single cell for comparison followed by a range?
Thank you for taking the time to answer!You would insert the formula in the "PointsOU" sheet, in the cell where you want to display the results. For example, if you want to display the results in cell C4, you would insert the following formula:
=SUMIF(Points!Last3Games, E2, IF(ISTEXT(Points!Last3Games), "", VLOOKUP(Points!Last3Games, Points!R:S, 2, FALSE))) + SUMIF(Points!Last3Games, H2, IF(ISTEXT(Points!Last3Games), "", VLOOKUP(Points!Last3Games, Points!R:S, 2, FALSE)))
If you want to display the results for the last 5 games, you would insert the following formula:
=SUMIF(Points!Last5Games, E2, IF(ISTEXT(Points!Last5Games), "", VLOOKUP(Points!Last5Games, Points!R:S, 2, FALSE))) + SUMIF(Points!Last5Games, H2, IF(ISTEXT(Points!Last5Games), "", VLOOKUP(Points!Last5Games, Points!R:S, 2, FALSE)))
And if you want to display the results for the last 10 games, you would insert the following formula:
=SUMIF(Points!Last10Games, E2, IF(ISTEXT(Points!Last10Games), "", VLOOKUP(Points!Last10Games, Points!R:S, 2, FALSE))) + SUMIF(Points!Last10Games, H2, IF(ISTEXT(Points!Last10Games), "", VLOOKUP(Points!Last10Games, Points!R:S, 2, FALSE)))
Please ensure that the references match the actual sheet and cell locations in your workbook.
Note that due to differences in syntax and functions between Excel and Google Sheets, complex formulas might need adjustment when moving between the two platforms.
I think I'm at my wits' end when it comes to Google Sheets. I would recommend additionally asking Google Sheets.
