VLOOKUP SUM with blank values

[tcarwardine]

Hi
I am building a school timetable and I am trying to SUM the weight of different subject choices based on a separate lookup table.

Student must have a weight of at least 3 but this can be a mix of any courses. There are four blocks and not every block will be filled by every pupil.

When I use VLOOKUP to SUM their block choices it is returning N/A because of the blank block columns.

The formula i tried is:
=VLOOKUP([@A],WEIGHT[#All],3,FALSE)+
VLOOKUP([@B],WEIGHT[#All],3,FALSE)+
VLOOKUP([@C],WEIGHT[#All],3FALSE)+
VLOOKUP([@D],WEIGHT[#All],3,FALSE)

This is my table 'OPTIONS'. The column I am trying to calculate is BLOCKED WEIGHT.
In this instance the top pupil should return 3. The second pupil 2. The third 2. And the fourth 2.

This is my lookup table 'WEIGHT'

Many thanks

 

[AlanY]

apology, miss read your question

 

[AlanY]

you can check if the cell is empty then proceed like this

=if(isblank(([@A]),0,VLOOKUP([@A],WEIGHT[#All],3,FALSE)) etc

 

[tcarwardine]

you can check if the cell is empty then proceed like this

=if(isblank(([@A]),0,VLOOKUP([@A],WEIGHT[#All],3,FALSE)) etc

Amazing , thanks Alan

 

[AlanY]

Amazing , thanks Alan

you're welcome