VLookup

I have 2 sheets, one (numerically named for the day of the month, in this example '1') has daily records on vehicle mileage, where column A is the vehicle registration number (VRM), column C the daily mileage.
Sheet 2 (Total) is a running total for the month of mileage for each vehicle.

My formula, placed in the Total sheet, is =IF(COUNTIF('1'!$A:$A,""),"",IF(COUNTIF('1'!$A:$A,Total!$A2),VLOOKUP(Total!$A2,'1'!$A:$C,3,FALSE),"0 "))

What I am trying to do is the following:

If the daily VRM list ('1'$A$A) is empty, then leave the cell null
ELSE
If the VRM that corresponds to my cell in the Total sheet (Total!$A2) is present in the daily VRM list then perform a VLOOKUP on column C of the daily sheet
ELSE
Enter a '0' value in the cell.

Except it's not working. Can anyone see from looking at the formula what is wrong? Or do you need to see examples?

could you post an example?

Try like this

=IF(COUNTA('1'!$A:$A),IF(COUNTIF('1'!$A:$A,Total!$A2),VLOOKUP(Total!$A2,'1'!$A:$C,3,FALSE),0),"")

Originally Posted by daddylonglegs

Try like this

=IF(COUNTA('1'!$A:$A),IF(COUNTIF('1'!$A:$A,Total!$A2),VLOOKUP(Total!$A2,'1'!$A:$C,3,FALSE),0),"")

Thanks, this works. However, I now appear to have a problem with the data I am importing. I have attached an exmple file.

Worksheet '1' has data I have manually keyed in and this is reflected in column 'D' of the Total sheet.

Worksheet '2' has data I have imported from the daily file and this returns zeros where I would expect in Totals column D (vehicle not in file) but the vehicles which ARE present in the daily file are producing an Invalid Cell Reference Error.

Can anyone tell me why?