#VALUE! error with vlookup formula

[EugeneZ]

Hi!

I have constructed a vlookup formula that looks up a date value from this sheet in an array on a separate work sheet and returns a time value in hours. I am accessing a number of cells on the sheet and am getting a #value error if any of them are blank. This is affecting other formulas in my workbook. Any ideas are very welcome! Thanks in advance!

Eugene

PS Formula here: =VLOOKUP($A18,Shane!$A$3:$H$347,4,FALSE)+VLOOKUP($A19,Shane!$A$3:$H$347,4,FALSE)+VLOOKUP($A20,Shane!$A$3:$H$347,4,FALSE)+VLOOKUP($A21,Shane!$A$3:$H$347,4,FALSE)+VLOOKUP($A22,Shane!$A$3:$H$347,4,FALSE)

 

[EugeneZ]

Hi Guys! Thanks for your help, your thinking put me on the right path and I managed to solve it. Here is the solution (not very elegant, but it works!):

=IF(VLOOKUP($A18,Shane!$A$3:$H$347,2,FALSE)=0,0,VLOOKUP($A18,Shane!$A$3:$H$347,4,FALSE))+IF(VLOOKUP($A19,Shane!$A$3:$H$347,2,FALSE)=0,0,VLOOKUP($A19,Shane!$A$3:$H$347,4,FALSE))+IF(VLOOKUP($A20,Shane!$A$3:$H$347,2,FALSE)=0,0,VLOOKUP($A20,Shane!$A$3:$H$347,4,FALSE))+IF(VLOOKUP($A21,Shane!$A$3:$H$347,2,FALSE)=0,0,VLOOKUP($A21,Shane!$A$3:$H$347,4,FALSE))+IF(VLOOKUP($A22,Shane!$A$3:$H$347,2,FALSE)=0,0,VLOOKUP($A22,Shane!$A$3:$H$347,4,FALSE))

 

[Ormus]

I had similar issues with Excel 03. To get around not having iferror, can do if(iserror