VLOOKUP but ignore blank cells?

[mabgilli]

Hi, I am a teacher and am trying to make a spreadsheet where one sheet displays grade values (letters) and the second sheet displays the numerical value of the corresponding cell (UK AS levels - A=60, B=50 etc.). I have used VLOOKUP to do this, however as each student only studies a particular number of subjects and the sheets show all subjects, I have lots of blanks on sheet 1 (Data by grade). When dragging the VLOOKUP formula across multiple cells it creates #N/A for the blanks, and then because it has already identified the error, doesn't even perform the VLOOKUP correctly for the cells that have values.

I have tried to combine ISBLANK with VLOOKUP, but can't figure out the order for this to work. I have searched other similar problems, but because they have different specifics I have found it impossible to apply their solutions to my problem.

The VLOOKUP formula I'm trying to work with: =VLOOKUP('Data by grade'!N2,Sheet3!A1:B5,2,FALSE)

Thanks,

Mike

 

[RCBricker]

you need to include an if isna.

using your formula it would be something like this. it will return nothing if the vlookup would normally return #N/A

=IF(ISNA(VLOOKUP('Data by grade'!N2,Sheet3!A1:B5,2,FALSE)),"",VLOOKUP('Data by grade'!N2,Sheet3!A1:B5,2,FALSE))

 

[Roger Govier]

Hi Mike
Your Grading range values should be absolute - Sheet3!$A$1:$B$5

Then maybe you should test the cell on Data by Grade first to see if it contains a value
=IF('Data by grade'!N2="","",VLOOKUP('Data by grade'!N2,Sheet3!$A$1:$B$5,2,FALSE)

 

[SpillerBD]

I'm thinking there may be better solutions than the VLOOKUP method you are currently attempting to use.
However those suggestions may be best made with a sample file of your current source data, with dummy values of course, with enough data and variance to provide a good testing source.

 

[mabgilli]

you need to include an if isna.

using your formula it would be something like this. it will return nothing if the vlookup would normally return #N/A

=IF(ISNA(VLOOKUP('Data by grade'!N2,Sheet3!A1:B5,2,FALSE)),"",VLOOKUP('Data by grade'!N2,Sheet3!A1:B5,2,FALSE))

Thanks for that, however because the VLOOKUP (when dragged across numerous cells to copy the formula) originally identifies an error and #N/As it, this is now causing all cells to return a blank, even cells which include data.

Any ideas?

 

[RCBricker]

hmmm works on my end. Be sure to wrap your range in $s to look in the range. but not criteria range as you want that to point to the correct cell for referencing.

or here I have done it for you

=IF(ISNA(VLOOKUP('Data by grade'!N2,Sheet3!$A$1:$B$5,2,FALSE)),"",VLOOKUP('Data by grade'!N2,Sheet3!$A$1:$B$5,2,FALSE))

this should work and bring back a value. As long as a cell would bring back a value and not #n/a.

rich

 

[mabgilli]

hmmm works on my end. Be sure to wrap your range in $s to look in the range. but not criteria range as you want that to point to the correct cell for referencing.

or here I have done it for you

=IF(ISNA(VLOOKUP('Data by grade'!N2,Sheet3!$A$1:$B$5,2,FALSE)),"",VLOOKUP('Data by grade'!N2,Sheet3!$A$1:$B$5,2,FALSE))

this should work and bring back a value. As long as a cell would bring back a value and not #n/a.

rich

That did the trick. Thanks a lot!