Ignoring a vlookup if no data

[Dazlancs]

Hi All,

I am trying to write a formula to create the sum of multiple VLOOKUPs. This is pretty simple with the basic knowledge i have however, i have a problem whereby the data points change on a daily basis so i need the vlookup to ignore any values that are not found and then countinue to count the sum.....

My current formula looks like this:

=IF('Raw Data'!A1=0,"N/A",IFERROR(VLOOKUP('Look Ups'!$A$67,'Raw Data'!C:I,2,FALSE)+IFERROR(VLOOKUP('Look Ups'!$A$68,'Raw Data'!C:I,2,FALSE)+IFERROR(VLOOKUP('Look Ups'!$A$69,'Raw Data'!C:I,2,FALSE)+IFERROR(VLOOKUP('Look Ups'!$A$70,'Raw Data'!C:I,2,FALSE)+IFERROR(VLOOKUP('Look Ups'!$A$71,'Raw Data'!C:I,2,FALSE)+IFERROR(VLOOKUP('Look Ups'!$A$72,'Raw Data'!C:I,2,FALSE)+IFERROR(VLOOKUP('Look Ups'!$A$73,'Raw Data'!C:I,2,FALSE)+IFERROR(VLOOKUP('Look Ups'!$A$74,'Raw Data'!C:I,2,FALSE)+IFERROR(VLOOKUP('Look Ups'!$A$75,'Raw Data'!C:I,2,FALSE)+IFERROR(VLOOKUP('Look Ups'!$A$76,'Raw Data'!C:I,2,FALSE)+IFERROR(VLOOKUP('Look Ups'!$A$77,'Raw Data'!C:I,2,FALSE)+IFERROR(VLOOKUP('Look Ups'!$A$78,'Raw Data'!C:I,2,FALSE)+IFERROR(VLOOKUP('Look Ups'!$A$79,'Raw Data'!C:I,2,FALSE)+IFERROR(VLOOKUP('Look Ups'!$A$80,'Raw Data'!C:I,2,FALSE)+IFERROR(VLOOKUP('Look Ups'!$A$81,'Raw Data'!C:I,2,FALSE)+IFERROR(VLOOKUP('Look Ups'!$A$82,'Raw Data'!C:I,2,FALSE)+IFERROR(VLOOKUP('Look Ups'!$A$83,'Raw Data'!C:I,2,FALSE),0),0),0),0),0),0),0),0),0),0),0),0),0),0),0),0),0))

This should total up all of the data points requested however, when it hits any of the lookups where there is no valid data, it stops the formula dead and gives the number at that point.

The above should total 1302 but only counts the first number of 406 from cell $A$67 and then stops (as the lookup for $A$68 doesn't exist today (but may tomorrow).

I need the formula to skip any formulas that can't find the lookup and move on to the next one.

Can anyone help please as this is doing my nut in

 

[Dazlancs]

Sorted I actually changed it in it's entirity