Multiple nested VLOOKUPs in IFERROR

Occasional Contributor

I'm sorry - it's been asked before and looked through the older posts but can't solve my problem. 

I'm looking to string together a formula which will look for a given name in 4 different "boxes" and return a corresponding value. I can get IFError and Vlookup to work but only for the first 2 "boxes"...

 

Also, I have a name with values in two "boxes" and would love for Excel to be able to return a sum of the two values without yet another step. 

I have attached a simplified dummy sheet for anybody who's able to help to play around with. 

Thanks in advance for any help you can give me!   

4 Replies

@RikkeB 

In C16:

 

=SUMIF($B$3:$B$10,B16,$F$3:$F$10)+SUMIF($H$3:$H$10,B16,$L$3:$L$10)+SUMIF($N$3:$N$10,B16,$R$3:$R$10)+SUMIF($T$3:$T$10,B16,$X$3:$X$10)

 

Fill down.

@RikkeB 

=SUMPRODUCT((($B$3:$B$10=B16)*($F$3:$F$10))+(($H$3:$H$10=B16)*($L$3:$L$10))+(($N$3:$N$10=B16)*($R$3:$R$10))+(($T$3:$T$10=B16)*($X$3:$X$10)))

 

Instead of vlookup you can apply sumproduct.

Thanks Hans ! Looks much easier than Vlookup...
Thanks Quadruple Pawn,
I have never used SumProduct before, so will look at what else I can use it for.