Ignore Row with Text Using VLOOKUP

multifidus · Dec 23, 2023

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: Example.xlsx

Thank you!

Alex Blakenburg · Dec 26, 2023

Can we rely on the numbers in column B ?
Would something like this work for you ?
Note: I have only provided the formulas in C4:Q4

20231226 Example-1.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

1

Firs col

10

2

S

3

Name

1 GA

2 GA

3 GA

4 GA

5 GA

6 GA

7 GA

8 GA

9 GA

10 GA

11 GA

12 GA

13 GA

14 GA

15 GA

First 'G'

G-Max

G-Start

Start-Row

Max-Row

4

Bey_Saddiq

Saddiq Bey

Points

Cell Formulas
Range	Formula
D1:Q1	D1	=C1+1
M2:Q2,C2:K2	M2	=SUBSTITUTE(ADDRESS(1,M1,4),"1","")
C4:Q4	C4	=LET(lkupArr,INDIRECT("'"&$A4&"'!"&"B"&$U4 & ":B" & $Y4), returnArr,INDIRECT("'"&$A4&"'!"&"AB"&$U4 & ":AB" & $Y4), XLOOKUP($V$4-(COLUMN()-3),lkupArr,returnArr,""))
U4	U4	=MATCH("G",INDIRECT("'"&A4&"'!B:B"),0)
V4	V4	=MAX(INDIRECT("'"&A4&"'!B"&U4&":B100000"))
W4	W4	=MAX(1,V4-2)
X4	X4	=MATCH(W4,INDIRECT("'"&A4&"'!B"&U4&":B100000"),0)+U4-1
Y4	Y4	=MATCH(V4,INDIRECT("'"&A4&"'!B"&U4&":B100000"),0)+U4-1

multifidus · Dec 26, 2023

Yes, this works!

Just out of curiosity, what part of the C4:Q4 formula skips the header rows?

Thank you so much!!

multifidus · Dec 26, 2023

Alex Blakenburg said:
Can we rely on the numbers in column B ?
Would something like this work for you ?
Note: I have only provided the formulas in C4:Q4

20231226 Example-1.xlsx
A B C D E F G H I J K L M N O P Q R S T U V W X Y
1 Firs col 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24
2 J K L M N O P Q R S T U V W X
3 Name 1 GA 2 GA 3 GA 4 GA 5 GA 6 GA 7 GA 8 GA 9 GA 10 GA 11 GA 12 GA 13 GA 14 GA 15 GA First 'G' G-Max G-Start Start-Row Max-Row
4 Bey_Saddiq Saddiq Bey 9.0 21.0 5.0 10.0 12.0 16.0 16.0 21.0 17.0 13.0 14.0 7.0 13.0 12.0 12.0 42 27 25 68 70
Points
Cell Formulas
Range Formula
D1:Q1 D1 =C1+1
M2:Q2,C2:K2 M2 =SUBSTITUTE(ADDRESS(1,M1,4),"1","")
C4:Q4 C4 =LET(lkupArr,INDIRECT("'"&$A4&"'!"&"B"&$U4 & ":B" & $Y4), returnArr,INDIRECT("'"&$A4&"'!"&"AB"&$U4 & ":AB" & $Y4), XLOOKUP($V$4-(COLUMN()-3),lkupArr,returnArr,""))
U4 U4 =MATCH("G",INDIRECT("'"&A4&"'!B:B"),0)
V4 V4 =MAX(INDIRECT("'"&A4&"'!B"&U4&":B100000"))
W4 W4 =MAX(1,V4-2)
X4 X4 =MATCH(W4,INDIRECT("'"&A4&"'!B"&U4&":B100000"),0)+U4-1
Y4 Y4 =MATCH(V4,INDIRECT("'"&A4&"'!B"&U4&":B100000"),0)+U4-1

I want to follow up with you because there is an odd result happening in the PointsOU sheet. I am using the following formula to check each column for that player in B3 to see if the games are over or under a value:
=IFERROR(IF(VLOOKUP($B3, Points!$B$4:$G$1229, 2,) >$E3, 1, 0) + IF(VLOOKUP($B3, Points!$B$4:$G$1229, 3,) >$E3, 1, 0)+ IF(VLOOKUP($B3, Points!$B$4:$G$1229, 4,) >$E3, 1, 0),"")

This is what I use for checking the last three games, as you can see I checked the second column, and if it was greater than the value add 1 and if not then 0, then move to the 3rd column, and 4th column.

I repeated/expanded this formula to check the last 5 games and it worked. I was trying to debug and I started adding just one additional column at a time and for whatever reason when I add the one additional column to check 6 games it gives me a blank (I tried every column after the 5th game just to see if any of them would work). For whatever reason when I try it with the last 10 games (or even just a 6th game) it does not work and returns a blank cell after applying the updated code (which now successfully just shows numbers in the Points sheet).

Do you have any idea why I am getting this issue of blanks after 5?

Here's my sample code of trying a 6th game:

=IFERROR(IF(VLOOKUP($B3, Points!$B$4:$G$1229, 2,) >$E3, 1, 0) + IF(VLOOKUP($B3, Points!$B$4:$G$1229, 3,) >$E3, 1, 0)+ IF(VLOOKUP($B3, Points!$B$4:$G$1229, 4,) >$E3, 1, 0)+ IF(VLOOKUP($B3, Points!$B$4:$G$1229, 5,) >$E3, 1, 0) + IF(VLOOKUP($B3, Points!$B$4:$G$1229, 6,) >$E3, 1, 0) + IF(VLOOKUP($B3, Points!$B$4:$G$1229, 7,) >$E3, 1, 0),"")

Alex Blakenburg · Dec 27, 2023

multifidus said:
Yes, this works!

Just out of curiosity, what part of the C4:Q4 formula skips the header rows?

The formula is doing a lookup on column B so it needs to find a matching number. The heading row has a G in column B so it will never match that row.

Alex Blakenburg · Dec 27, 2023

multifidus said:
I want to follow up with you because there is an odd result happening in the PointsOU sheet. I am using the following formula to check each column for that player in B3 to see if the games are over or under a value:
=IFERROR(IF(VLOOKUP($B3, Points!$B$4:$G$1229, 2,) >$E3, 1, 0) + IF(VLOOKUP($B3, Points!$B$4:$G$1229, 3,) >$E3, 1, 0)+ IF(VLOOKUP($B3, Points!$B$4:$G$1229, 4,) >$E3, 1, 0),"")

Your lookup array is B:G and that is only 6 columns, so when you put in values > 6 for the column no it will error out. You would need to expand your range to B:Q

You could also try something like this:
PS: You only have > and < so you are not covering off "="

20231226 Example-1.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

P

Q

R

S

T

U

V

W

X

Y

1

2

Player

Line A

Line B

L3 Over

L3 Under

L5 Over

L5 Under

L10 Over

L10 Under

L15 Over

L15 Under

3

Saddiq Bey

13.5

-120

-110

13.5

-113

-115

PointsOU

Cell Formulas
Range	Formula
R3:Y3	R3	=LET(cols,--MID(R$2,2,2), compare,TRIM(RIGHT(R$2,5)), fltrRow,FILTER(Points!$C$3:$Q$20,--(Points!$B$3:$B$20=$B3),""), fltrCols,FILTER(fltrRow,(COLUMN(Points!$C$3:$Q$3)-2<=cols),""), IF(compare="Over",SUM(--(fltrCols>$E3)),SUM(--(fltrCols<$E3))))

multifidus · Dec 28, 2023

I feel pretty dumb not realizing that my array only went to G! Thank you so much for all of your help, I really appreciate you taking the time!

Alex Blakenburg · Dec 28, 2023

You're welcome. Glad I could help.

Ignore Row with Text Using VLOOKUP

multifidus

New Member

Excel Facts

Alex Blakenburg

MrExcel MVP

multifidus

New Member

multifidus

New Member

Alex Blakenburg

MrExcel MVP

Alex Blakenburg

MrExcel MVP

multifidus

New Member

Alex Blakenburg

MrExcel MVP

Similar threads

Forum statistics

Share this page

Ignore Row with Text Using VLOOKUP

New Member

Excel Facts

MrExcel MVP

New Member

New Member

MrExcel MVP

MrExcel MVP

New Member

MrExcel MVP

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock