Vlookup formula across multiple tables #N/A when lookup not present

[Beh162]

My goal to count how many Blocks a certain EE# has across 7 days, but there are days where they may not be present in the data. The current formula i'm using =SUM(VLOOKUP(B3,$B$18:$E$73,2,),VLOOKUP(B3,$H$18:$K$73,2,0),VLOOKUP(B3,$N$18:$Q$73,2,0),VLOOKUP(B3,$T$18:$W$73,2,0),VLOOKUP(B3,$Z$18:$AC$73,2,0),VLOOKUP(B3,$AF$18:$AI$73,2,0),VLOOKUP(B3,$AL$18:$AO$73,2,0))

Gives me an error of #N/A if one of the days that lookup value EE 13 isn't present. Should I be using a better option?

In my screen shot H16 doesn't have #13 so it's giving me the error. Thanks!

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[6StringJazzer]

What version of Excel are you using? (I recommend updating your user profile to show this.) The answer might be different for recent versions vs. older ones.

You should bracket each VLOOKUP with

IFERROR(VLOOKUP(B3,$B$18:$E$73,2,),0)



From the limited glimpse I get from your screenshot, I would say your data design is a problem. If you redesign your data layout this could be done with a single SUMIF function.

 

[Beh162]

What version of Excel are you using? (I recommend updating your user profile to show this.) The answer might be different for recent versions vs. older ones.

You should bracket each VLOOKUP with

IFERROR(VLOOKUP(B3,$B$18:$E$73,2,),0)



From the limited glimpse I get from your screenshot, I would say your data design is a problem. If you redesign your data layout this could be done with a single SUMIF function.

This solved my problem! Thank you!