Vlookup Encounters a call with no data and returns #N/A

  • Thread starter Thread starter Six Sigma Blackbelt
  • Start date Start date
S

Six Sigma Blackbelt

My vlookup formula goes like this

=VLOOKUP($A2,December!$C$3:$H$68,3,FALSE)+VLOOKUP($A2,January!$C$3:$H$65,3,FALSE)+VLOOKUP($A2,February!$C$3:$H$74,3,FALSE)+VLOOKUP($A2,March!$C$3:$H$85,3,FALSE)+VLOOKUP($A2,April!$C$3:$H$85,3,FALSE)

The formula finds a name an adds the numbers associated with that name in
the cells together, but some of the months like April do not have the names
of the people so the vlookup returns #N/A instead of the sum, because that
name could not be found in the specified tab labeled March or April. What
kind of IF Statement can i use to continue adding the cells even if that
persons name dosen't show up in that tab.
 
IF(ISERROR(+vlookupformula1),"",+vlookupformula1)+IF(ISERROR(vlookupformula2),"",vlookformula2) and so on.
 
Im getting an error on the first set of quotation mark, I can send you the
screen shot of what it is saying if that would help

=IF(ISERROR(+VLOOKUP($A2,December!$C$3:$H$68,3,FALSE),"",+VLOOKUP($A2,December!$C$3:$H$68,3,False)+IFSERROR($A2,April!$C$3:$H$88,3,FALSE),"",+vlookup($A2,April!$C$3:$H$88,3,False))
 
=VLOOKUP($A2,December!$C$3:$H$68,3,FALSE)
+VLOOKUP($A2,January!$C$3:$H$65,3,FALSE)
+VLOOKUP($A2,February!$C$3:$H$74,3,FALSE)
+VLOOKUP($A2,March!$C$3:$H$85,3,FALSE)
+VLOOKUP($A2,April!$C$3:$H$85,3,FALSE)

You could check to see if each addend is an error. If it is, then return 0:

=if(isna(VLOOKUP(...)),0,vlookup(...))
+if(isna(VLOOKUP(...)),0,vlookup(...))
....
 
Dave Thanks, that help, but when a persons name isnt in the sheet it returns
a 0 and dosent continue to add the cells in the multiple sheets. So basically
i need the formula to skip that sheet if the names dosent appear in the sheet
and move on to the next sheet, sum the cell with other cells that is has
already sum and continue the process until all sheets have been sum.
 
You have to change each =vlookup() in your formula so that it checks for an
error. If it's an error, return a 0.

=if(isna(VLOOKUP($A2,December!$C$3:$H$68,3,FALSE)),0,
VLOOKUP($A2,December!$C$3:$H$68,3,FALSE))

+if(isna(VLOOKUP($A2,January!$C$3:$H$65,3,FALSE)),0,
VLOOKUP($A2,January!$C$3:$H$65,3,FALSE))

+ ...

If that doesn't work, post the formula that you tried.
 
Hi

You could do it a different way.
On your Summary sheet, list your Sheet names - I used F1:F4 - then the
formula
=SUMPRODUCT((SUMIF(INDIRECT($F$1:$F$4&"!C3:C85"),A2,INDIRECT($F$1:$F$4&"!E3:E85"))))
 
Thanks Roger, but how can the formula grab the right info, with the vlookup,
its searching by name then going to the assigned row to find the number,
adding that number to the next number in the next sheet by the same name,
then I want it to skip the sheet if the name dosen't appear on the sheet and
move to the next to continue the process for the selected sheets
 
Dave it works to a degree, it seems like I can only have so many vlookups in
the formula, then its tells me there is an error in thr formula, when i take
the last vlookup out, it works. It IDs the cell, sums it and when the name
isnt in the sheet it moves to the next sheet. The formula is posted below.
Thanks again for the help, I have come farther in the last 2 hours than the
whole day.

=IF(ISNA(VLOOKUP($A2,December!$C$4:$Q$69,15,FALSE)),0,(VLOOKUP($A2,December!$C$4:$Q$69,15,FALSE))+IF(ISNA(VLOOKUP($A2,January!$C$3:$Q$75,15,FALSE)),0,(VLOOKUP($A2,January!$C$4:$Q$75,15,FALSE))+IF(ISNA(VLOOKUP($A2,February!$C$3:$Q$85,15,FALSE)),0,(VLOOKUP($A2,February!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,March!$C$3:$Q$85,15,FALSE)),0,(VLOOKUP($A2,March!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,April!$C$4:$Q$85,15,FALSE)),0,(VLOOKUP($A2,April!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,May!$C$4:$Q$85,15,FALSE)),0,(VLOOKUP($A2,May!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,June!$C$4:$Q$85,15,FALSE)),0,(VLOOKUP($A2,June!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,July!$C$4:$Q$85,15,FALSE)),0,(VLOOKUP($A2,July!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,August!$C$4:$Q$85,15,FALSE)),0,(VLOOKUP($A2,August!$C$4:$Q$85,15,FALSE)))))))))
 
Dave it works to a degree, it seems like I can only have so many vlookups in
the formula, then its tells me there is an error in thr formula, when i take
the last vlookup out, it works. It IDs the cell, sums it and when the name
isnt in the sheet it moves to the next sheet. The formula is posted below.
Thanks again for the help, I have come farther in the last 2 hours than the
whole day.

=IF(ISNA(VLOOKUP($A2,December!$C$4:$Q$69,15,FALSE)),0,(VLOOKUP($A2,December!$C$4:$Q$69,15,FALSE))+IF(ISNA(VLOOKUP($A2,January!$C$3:$Q$75,15,FALSE)),0,(VLOOKUP($A2,January!$C$4:$Q$75,15,FALSE))+IF(ISNA(VLOOKUP($A2,February!$C$3:$Q$85,15,FALSE)),0,(VLOOKUP($A2,February!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,March!$C$3:$Q$85,15,FALSE)),0,(VLOOKUP($A2,March!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,April!$C$4:$Q$85,15,FALSE)),0,(VLOOKUP($A2,April!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,May!$C$4:$Q$85,15,FALSE)),0,(VLOOKUP($A2,May!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,June!$C$4:$Q$85,15,FALSE)),0,(VLOOKUP($A2,June!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,July!$C$4:$Q$85,15,FALSE)),0,(VLOOKUP($A2,July!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,August!$C$4:$Q$85,15,FALSE)),0,(VLOOKUP($A2,August!$C$4:$Q$85,15,FALSE)))))))))
 
You could always use multiple cells (on per sheet???).

Then use =sum() to add up all those helper cells.

And hide the columns with the helper cells.


Dave it works to a degree, it seems like I can only have so many vlookups in
the formula, then its tells me there is an error in thr formula, when i take
the last vlookup out, it works. It IDs the cell, sums it and when the name
isnt in the sheet it moves to the next sheet. The formula is posted below.
Thanks again for the help, I have come farther in the last 2 hours than the
whole day.

=IF(ISNA(VLOOKUP($A2,December!$C$4:$Q$69,15,FALSE)),0,(VLOOKUP($A2,December!$C$4:$Q$69,15,FALSE))+IF(ISNA(VLOOKUP($A2,January!$C$3:$Q$75,15,FALSE)),0,(VLOOKUP($A2,January!$C$4:$Q$75,15,FALSE))+IF(ISNA(VLOOKUP($A2,February!$C$3:$Q$85,15,FALSE)),0,(VLOOKUP($A2,February!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,March!$C$3:$Q$85,15,FALSE)),0,(VLOOKUP($A2,March!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,April!$C$4:$Q$85,15,FALSE)),0,(VLOOKUP($A2,April!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,May!$C$4:$Q$85,15,FALSE)),0,(VLOOKUP($A2,May!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,June!$C$4:$Q$85,15,FALSE)),0,(VLOOKUP($A2,June!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,July!$C$4:$Q$85,15,FALSE)),0,(VLOOKUP($A2,July!$C$4:$Q$85,15,FALSE))+IF(ISNA(VLOOKUP($A2,August!$C$4:$Q$85,15,FALSE)),0,(VLOOKUP($A2,August!$C$4:$Q$85,15,FALSE)))))))))
Click to expand...
 
Just try it and see.
It doesn't matter if the name doesn't exist on a sheet. If it's not there,
there will be no associated value in column E.
 
Back
Top