Removing #value! error in Vlookup

robert k

New Member
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

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the \$ sign).

DonkeyOte

MrExcel MVP
=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...

Last edited:

RoryA

MrExcel MVP, Moderator
Try:
Code:
``=IF(ISERROR(VLOOKUP(C1,'[Weekly.xls]Week1'!\$C:\$D,1,FALSE)),0,VLOOKUP(C350,'[Weekly.xls]Week1'!\$C:\$M,11,FALSE))``
HTH

Joesli

New Member
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:

robert k

New Member
Thanks lasw10, rorya this worked a treat.

Stormseed

Banned
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

MrExcel MVP, Moderator
Actually it is true. IFERROR is a new function in Excel 2007 whereby instead of writing something like:
Code:
``=IF(ISERROR(formula),0,formula)``
you can just write:
Code:
``=IFERROR(formula,0)``
to achieve the same thing.
HTH

Joesli

New Member
: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...:

kind regards,

jacob michaelsen,
denmark

Stormseed

Banned
Actually it is true. IFERROR is a new function in Excel 2007 whereby instead of writing something like:
Code:
``=IF(ISERROR(formula),0,formula)``
you can just write:
Code:
``=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

Replies
5
Views
80
Replies
7
Views
451
Replies
3
Views
79
Replies
0
Views
115
Replies
17
Views
182

1,190,877
Messages
5,983,361
Members
439,840
Latest member
billy1989

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.

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

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