A simpler way to do nested vlookups?

**bsoper** · 01-05-2011, 01:54 PM

Hi, it's me again! I have another question.

On my third tab, "Dept. E&F Summary," I have a huge formula that will sum up all the funding sources and expenditures if the date matches. This formula is pretty huge. I just added space for 10 more funding sources/expenditures, if I added 10 more vlookups inside the already huge formula it would be a rather gargantuan formula to deal with. I am wondering if there is an easier way to accomplish this same thing without the gargantuan formula.

On the second tab, "Proj. Detail Report," I have something similar, but I am using a match and then I will hide the text that it is matching by coloring the text white. There might be an easier way to do that as well, but this works ok.

Thanks for any help you can provide.

**NBVC** · 01-05-2011, 02:08 PM

Does something like this work, in E17 copied down and across?

=SUMPRODUCT((RawData!$C$2:$C$2000=$B17)*(RawData!$X$1:$AJ$1=E$16),RawData!$Z$2:$AL$2000)

**bsoper** · 01-06-2011, 03:11 PM

It isn't returning anything, just 0's. I don't follow the "RawData!$X$1:$AJ$1=E$16" part, as row 1 isn't ever equal to the date in E16, it's the header row.

I have updated the RawData tab with the additional columns I had stated in the previous post, so I attached the updated file.

**NBVC** · 01-06-2011, 03:19 PM

Things have changed, positionwise....

Try for Expenditure:

=SUMPRODUCT((RawData!$B$2:$B$2000=$B17)*(RawData!$N$2:$AO$2000=E$16),RawData!$P$2:$AQ$2000)

and for Funding

=SUMPRODUCT((RawData!$B$2:$B$2000=$B36)*(RawData!$AS$2:$CI$2000=E$16),RawData!$AU$2:$CK$2000)