#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)
 

Some videos you may like

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,194
Use IFERROR function in between your vlookup function.

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

EugeneZ

New Member
Joined
Nov 4, 2011
Messages
6
Hi Vis! Thanks for your quick reply. Now getting a #name? error. I am doing this in Excel 2003. Did iserror exist then? :confused:
 

EugeneZ

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

xtremegrump

New Member
Joined
Jan 7, 2011
Messages
45

ADVERTISEMENT

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
 

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,194
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)
 

EugeneZ

New Member
Joined
Nov 4, 2011
Messages
6

ADVERTISEMENT

Hi Xtreme! Sorry, nice try, didn't work either ... any other suggestions? :confused:
 

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,194
Or Try this

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

vds1

Well-known Member
Joined
Oct 5, 2011
Messages
1,194
Provide few sample data. Let me have look into it.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,259
Messages
5,600,575
Members
414,390
Latest member
plimbu

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
Top