Forum Discussion
Multiple nested VLOOKUPs in IFERROR
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
=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 Quadruple Pawn,
I have never used SumProduct before, so will look at what else I can use it for.
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.
- Thanks Hans ! Looks much easier than Vlookup...
