Forum Discussion
Ignore Row with Text Using VLOOKUP
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.