Forum Discussion
VLookup to ignore values with blank cells
Is it possible to make Vlookup ignore lookup values with adjacent blank cells? Here's the problem:
VLookup returns data from a named range to the bottom list of teams. But I only need the data from the teams in the top table that have scores next to them, ignoring the teams that don't have scores in adjacent cells. How do I do that? Is there an IFNumber function I can put into VLookup to just return data from named range with the teams that have scores? Thanks in advance.
Considering you are on cell B94 (Charlotte Hornets)
Try instead ISBLANK() formula confirm like below.
=IF(B2="","",VLOOKUP(A94,TEAMPOWER_AVGSEASON,2,FALSE))Ps.: As I'm not sure about the data of the named range TEAMPOWER_AVGSEASON, I considered the 2nd column as the OFF values.
9 Replies
I figured it out:
you can use the formula in the image using filter but not be able to use the named range or you can use:
=VLOOKUP(A96,TEAMPOWER_AVGSEASON,6,0)
=VLOOKUP(A96,TEAMPOWER_AVGSEASON,7,0)
=VLOOKUP(D96,TEAMPOWER_AVGSEASON,8,0)
=VLOOKUP(D96,TEAMPOWER_AVGSEASON,9,0)
respectively in that order from left to right see image below:
File attached below
Yea_So Thank you for your work on this, Yea_So. You have been incredibly helpful. Juliano-Petrukio solved the problem and just made my sheet work perfectly.
I haven't touched VLOOKUP for years but turning the team names without an associated score to FALSE would prevent the lookup returning data for it, e.g.
= VLOOKUP(lookupVal, IF(ISNUMBER(scores),table), 2, 0)
- Can you please add some file containing the sample data instead just the image?
It will help us to assist you.I would like VLookup only to return data from named range from teams with scores in adjacent cells and to ignore teams with blank cells or no scores in above range.
Considering you are on cell B94 (Charlotte Hornets)
Try instead ISBLANK() formula confirm like below.
=IF(B2="","",VLOOKUP(A94,TEAMPOWER_AVGSEASON,2,FALSE))Ps.: As I'm not sure about the data of the named range TEAMPOWER_AVGSEASON, I considered the 2nd column as the OFF values.
