#VALUE! error with vlookup formula

EugeneZ

New Member
Joined
Nov 4, 2011
Messages
6
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)
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number
Use IFERROR function in between your vlookup function.

Code:
=IFERROR(VLOOKUP($A18,Shane!$A$3:$H$347,4,FALSE),0)
 
Upvote 0
Hi Vis! Thanks for your quick reply. Now getting a #name? error. I am doing this in Excel 2003. Did iserror exist then? :confused:
 
Upvote 0
If I use just 1 vlookup, it returns the correct value. As soon as I add a 2nd vlookup it gives me the error. BTW I did mean iferror in my last post ...
 
Upvote 0
Give this a try.

=VLOOKUP($A12,Shane!$A$1:$H$347,4,0)+(VLOOKUP($A13,Shane!$A$1:$H$347,4,0))+(VLOOKUP($A14,Shane!$A$1:$H$347,4,0))+(VLOOKUP($A15,Shane!$A$1:$H$347,4,0))+(VLOOKUP($A16,Shane!$A$1:$H$347,4,0))

Hope this helps,
XtremeGrump
 
Upvote 0
Try this

Code:
=IF(TYPE(VLOOKUP($A18,Shane!$A$3:$H$347,4,FALSE)=16,TIME(0,0,0),VLOOKUP($A18,Shane!$A$3:$H$347,4,FALSE)+IF(TYPE(VLOOKUP($A19,Shane!$A$3:$H$347,4,FALSE)=16,TIME(0,0,0),VLOOKUP($A19,Shane!$A$3:$H$347,4,FALSE)+IF(TYPE(VLOOKUP($A20,Shane!$A$3:$H$347,4,FALSE)=16,TIME(0,0,0),VLOOKUP($A20,Shane!$A$3:$H$347,4,FALSE)+IF(TYPE(VLOOKUP($A21,Shane!$A$3:$H$347,4,FALSE)=16,TIME(0,0,0),VLOOKUP($A21,Shane!$A$3:$H$347,4,FALSE)+IF(TYPE(VLOOKUP($A22,Shane!$A$3:$H$347,4,FALSE)=16,TIME(0,0,0),VLOOKUP($A22,Shane!$A$3:$H$347,4,FALSE)
 
Upvote 0
Or Try this

Code:
=IFERROR(VLOOKUP($A18,Shane!$A$3:$H$347,4,FALSE),timevalue(0,0,0))
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,949
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top