Removing #value! error in Vlookup

[robert k]

Hi

I have created the below formula to link weekly information into a weekly summary spreadsheet, columns C and D contain a list of people. My formula works but in some circumstances where people are not included in one week the formula will return a #value! error. I have tried using ISERROR at the beginning of the formula but this only returns 0 in all cases.

=IF(VLOOKUP(C1,'[Weekly.xls]Week1'!$C:$D,1,FALSE),VLOOKUP(C350,'[Weekly.xls]Week1'!$C:$M,11,FALSE),"0")

Any suggestions on how this error can be removed?

Thanks

 

[DonkeyOte]

=IF(ISERROR(MATCH(C1,'[Weekly.xls]Week1'!$C:$C,0)),0,VLOOKUP(C350,'[Weekly.xls]Week1'!$C:$M,11,FALSE))

EDIT: if you're returning a value from column M you could in theory streamline to

=ISERROR(MATCH(C1,'[Weekly.xls]Week1'!$C:$C,0)=FALSE)*VLOOKUP(C350,'[Weekly.xls]Week1'!$C:$M,11,FALSE)

ie multiply the resulting VLOOKUP value by either 1/0 --> 0 where no match found though you would be doing the VLOOKUP regardless of whether or not it's really required...

 

[RoryA]

Try:

=IF(ISERROR(VLOOKUP(C1,'[Weekly.xls]Week1'!$C:$D,1,FALSE)),0,VLOOKUP(C350,'[Weekly.xls]Week1'!$C:$M,11,FALSE))

HTH

 

[Joesli]

Ive had the same problem. I was recommended IFERROR. however that only works with excel 2007 editions and above.

The solution is ISNA (Is Not Available):

=IF(ISNA(VLOOKUP());0;VLOOKUP())... only just remember to enter FALSE at the end of each VLOOKUP function!

My function ended up looking like this:

=+IF(ISNA(VLOOKUP(A8;'Y:\CERACO\Årsregnskab 2008\regnskab 2008\HTML Balancer Fra Navision\[Amaliegade.xls]Amaliegade'!$A$11:$M$230;13;FALSE));0;VLOOKUP(A8;'Y:\CERACO\Årsregnskab 2008\regnskab 2008\HTML Balancer Fra Navision\[Amaliegade.xls]Amaliegade'!$A$11:$M$230;13;FALSE))

 

[robert k]

Thanks lasw10, rorya this worked a treat.

 

[Stormseed]

I was recommended IFERROR. however that only works with excel 2007 editions and above

hi friend,

what you are referring to is not very true. ISERROR() is used to replace any and all types of formula error while ISNA() is used to replace errors which display #N/A in a formula. Also, this works in any version of Excel including Excel 2007.

 

[RoryA]

Actually it is true. IFERROR is a new function in Excel 2007 whereby instead of writing something like:

=IF(ISERROR(formula),0,formula)

you can just write:

=IFERROR(formula,0)

to achieve the same thing.
HTH

 

[Joesli]

:D

It would appear as you guys "are skilled in the art of excel" (to paraphrase the old japs)...

if you got your game down in writing macros as well i would greatly appreciate it if you could pop by the following link with some comments...:

http://www.mrexcel.com/forum/showthread.php?p=1615381#post1615381

kind regards,

jacob michaelsen,
denmark

 

[Stormseed]

Actually it is true. IFERROR is a new function in Excel 2007 whereby instead of writing something like:

=IF(ISERROR(formula),0,formula)

you can just write:

=IFERROR(formula,0)

to achieve the same thing.
HTH

well, not quite acquainted with Excel 2007, have got it on my PC @ home, yet to discover and explore the Office Suite