CODEWITHSUNDEEP
excelsumexcel-2007vlookup
Gokotai
Gokotai

Reputation: 153

sumproduct and vlookup returning an error

I have a function that reads:

=SUMPRODUCT(VLOOKUP(D261, '[IJM current plan.xls]LO calcs'!$AU$12:$BO$147,{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21},FALSE)+VLOOKUP(D261,'[IJM current plan.xls]OF calcs'!$BC$12:$CA$147,{2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23,24,25},FALSE))

But this returns an error.

The aim is to add up the rows between AU12 and BO12 in the LO calcs tab as well as the rows between BC12 and CA12 in the OF calcs tab if the row contains the value in cell D261 (a date).

This returns an #N/A error and I'm not sure why, as this exact same method works for two other spreadsheets but not for IJM current plan.

I have checked that the names, file paths, rows and columns are correct, and that any hidden rows, columns or tabs are clearly visible.

Any ideas?

Upvotes: 0

Views: 170

Answers (1)

Dmitry Pavliv
Dmitry Pavliv

Reputation: 35843

Try this one:

=IFERROR(SUM(INDEX('[IJM current plan.xls]LO calcs'!$AV$12:$BO$147,
             MATCH(D261,'[IJM current plan.xls]LO calcs'!$AU$12:$AU$147,0),0)),0)
+IFERROR(SUM(INDEX('[IJM current plan.xls]OF calcs'!$BD$12:$CA$147,
             MATCH(D261,'[IJM current plan.xls]OF calcs'!$BC$12:$BC$147,0),0)),0)

Upvotes: 2

Related Questions