SOLVED

VLookup to ignore values with blank cells

%3CLINGO-SUB%20id%3D%22lingo-sub-2901907%22%20slang%3D%22en-US%22%3EVLookup%20to%20ignore%20values%20with%20blank%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2901907%22%20slang%3D%22en-US%22%3E%3CP%3EIs%20it%20possible%20to%20make%20Vlookup%20ignore%20lookup%20values%20with%20adjacent%20blank%20cells%3F%20Here's%20the%20problem%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Screenshot%20(8).png%22%20style%3D%22width%3A%20999px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F321587i0C9C54EBE2E86418%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Screenshot%20(8).png%22%20alt%3D%22Screenshot%20(8).png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EVLookup%20returns%20data%20from%20a%20named%20range%20to%20the%20bottom%20list%20of%20teams.%20But%20I%20only%20need%20the%20data%20from%20the%20teams%20in%20the%20top%20table%20that%20have%20scores%20next%20to%20them%2C%20ignoring%20the%20teams%20that%20don't%20have%20scores%20in%20adjacent%20cells.%20How%20do%20I%20do%20that%3F%20Is%20there%20an%20IFNumber%20function%20I%20can%20put%20into%20VLookup%20to%20just%20return%20data%20from%20named%20range%20with%20the%20teams%20that%20have%20scores%3F%20Thanks%20in%20advance.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2901907%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2901941%22%20slang%3D%22en-US%22%3ERe%3A%20VLookup%20to%20ignore%20values%20with%20blank%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2901941%22%20slang%3D%22en-US%22%3ECan%20you%20please%20add%20some%20file%20containing%20the%20sample%20data%20instead%20just%20the%20image%3F%3CBR%20%2F%3EIt%20will%20help%20us%20to%20assist%20you.%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2902031%22%20slang%3D%22en-US%22%3ERe%3A%20VLookup%20to%20ignore%20values%20with%20blank%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2902031%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767933%22%20target%3D%22_blank%22%3E%40Juliano-Petrukio%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BI%20would%20like%20VLookup%20only%20to%20return%20data%20from%20named%20range%20from%20teams%20with%20scores%20in%20adjacent%20cells%20and%20to%20ignore%20teams%20with%20blank%20cells%20or%20no%20scores%20in%20above%20range.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2902441%22%20slang%3D%22en-US%22%3ERe%3A%20VLookup%20to%20ignore%20values%20with%20blank%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2902441%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1188001%22%20target%3D%22_blank%22%3E%40Mr_McSquirrelly%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20haven't%20touched%20VLOOKUP%20for%20years%20but%20turning%20the%20team%20names%20without%20an%20associated%20score%20to%20FALSE%20would%20prevent%20the%20lookup%20returning%20data%20for%20it%2C%20e.g.%3C%2FP%3E%3CP%3E%3D%20VLOOKUP(lookupVal%2C%20IF(ISNUMBER(scores)%2Ctable)%2C%202%2C%200)%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2903387%22%20slang%3D%22en-US%22%3ERe%3A%20VLookup%20to%20ignore%20values%20with%20blank%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2903387%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1188001%22%20target%3D%22_blank%22%3E%40Mr_McSquirrelly%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EConsidering%20you%20are%20on%20cell%20B94%20(Charlotte%20Hornets)%3C%2FP%3E%3CP%3ETry%20instead%20ISBLANK()%20formula%20confirm%20like%20below.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DIF(B2%3D%22%22%2C%22%22%2CVLOOKUP(A94%2CTEAMPOWER_AVGSEASON%2C2%2CFALSE))%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPs.%3A%20As%20I'm%20not%20sure%20about%20the%20data%20of%20the%20named%20range%26nbsp%3BTEAMPOWER_AVGSEASON%2C%20I%20considered%20the%202nd%20column%20as%20the%20OFF%20values.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2927901%22%20slang%3D%22en-US%22%3ERe%3A%20VLookup%20to%20ignore%20values%20with%20blank%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2927901%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767933%22%20target%3D%22_blank%22%3E%40Juliano-Petrukio%3C%2FA%3E%26nbsp%3BThat%20is%20the%20solution%20I%20was%20looking%20for!%20I%20could%20not%20find%20the%20email%20I%20used%20for%20my%20original%20account%2C%20so%20I%20started%20a%20new%20account.%20Many%2C%20many%20thanks%20Juliano-Petrukio!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2927907%22%20slang%3D%22en-US%22%3ERe%3A%20VLookup%20to%20ignore%20values%20with%20blank%20cells%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2927907%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047584%22%20target%3D%22_blank%22%3E%40Yea_So%3C%2FA%3E%26nbsp%3B%20Thank%20you%20for%20your%20work%20on%20this%2C%20Yea_So.%20You%20have%20been%20incredibly%20helpful.%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767933%22%20target%3D%22_blank%22%3E%40Juliano-Petrukio%3C%2FA%3E%26nbsp%3Bsolved%20the%20problem%20and%20just%20made%20my%20sheet%20work%20perfectly.%3C%2FP%3E%3C%2FLINGO-BODY%3E
Contributor

Is it possible to make Vlookup ignore lookup values with adjacent blank cells? Here's the problem:

 

Screenshot (8).png

 

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.

9 Replies
Can you please add some file containing the sample data instead just the image?
It will help us to assist you.

@Juliano-Petrukio 

 

     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.

@Mr_McSquirrelly 

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)

best response confirmed by allyreckerman (Microsoft)
Solution

@Mr_McSquirrelly 

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.

@Mr_McSquirrelly 

 

I figured it out:

Yea_So_0-1635642025531.png

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:

Yea_So_1-1635642928302.png

File attached below

@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!

@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.

Very good to hear that.
If it worked fine, flag it as solved.

@Juliano-Petrukio Here is the solution to the problem I was looking for. Thanks again!