Oct 29 2021 12:41 PM
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.
Oct 29 2021 12:46 PM
Oct 29 2021 01:06 PM
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.
Oct 29 2021 02:53 PM - edited Oct 29 2021 02:53 PM
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)
Oct 30 2021 03:23 AM - edited Oct 30 2021 03:28 AM
SolutionConsidering 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.
Oct 30 2021 06:19 PM
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
Nov 04 2021 10:26 AM
@Juliano-Petrukio That is the solution I was looking for! I could not find the email I used for my original account, so I started a new account. Many, many thanks Juliano-Petrukio!
Nov 04 2021 10:27 AM
@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.
Nov 04 2021 10:37 AM
Nov 04 2021 11:39 PM
@Juliano-Petrukio Here is the solution to the problem I was looking for. Thanks again!
Oct 30 2021 03:23 AM - edited Oct 30 2021 03:28 AM
SolutionConsidering 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.