Using =IF(ISNA function with vlookup

[jschoon4]

I am trying to use the =IF(ISNA function with vlookup to put the COST from sheet two on to sheet one based on the MFg#. As you can see Sheet two does not contain Mfg# 33331. My function does return a 0, but the My question is why does it return a 0 for the rows below it when in fact those Mfg# are listed in Sheet 2?
Here is my function:=IF(ISNA(VLOOKUP(A2:A6,Sheet2!A1:B4,2,0)),"0",VLOOKUP(A2:A6,Sheet2!A1:B4,2,0))
I start this function on cell E2 then just copy the function down when I do you can see the result I get.
It should only return a 0 for item 33331 and not 4444 and 5555.

Sheet 1
A B C D E
[TABLE="width: 355"]
<colgroup><col><col><col><col><col></colgroup><tbody>[TR]
[TD] Mfg #
[/TD]
[TD] cost
[/TD]
[TD]markup[/TD]
[TD]Final Price[/TD]
[TD]Premier Price[/TD]
[/TR]
[TR]
[TD="align: right"]1111
[/TD]
[TD="align: right"]$4.70[/TD]
[TD="align: right"]3.75[/TD]
[TD="align: right"]4.88[/TD]
[TD="align: right"]$4.70[/TD]
[/TR]
[TR]
[TD="align: right"]2222[/TD]
[TD="align: right"]$4.70[/TD]
[TD="align: right"]3.75[/TD]
[TD="align: right"]4.88[/TD]
[TD="align: right"]$4.70
[/TD]
[/TR]
[TR]
[TD="align: right"]33331[/TD]
[TD="align: right"]$4.40[/TD]
[TD="align: right"]3.75[/TD]
[TD="align: right"]4.55[/TD]
[TD]0 0
[/TD]
[/TR]
[TR]
[TD="align: right"]4444
[/TD]
[TD="align: right"]$4.40[/TD]
[TD="align: right"]3.75[/TD]
[TD="align: right"]4.55[/TD]
[TD]0 0
[/TD]
[/TR]
[TR]
[TD="align: right"]5555[/TD]
[TD="align: right"]$10.00[/TD]
[TD="align: right"]3.75[/TD]
[TD="align: right"]10.38[/TD]
[TD]0 0
[/TD]
[/TR]
</tbody>[/TABLE]

Sheet 2
A B
[TABLE="width: 128"]
<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>[TR]
[TD="width: 64, align: right"]1111[/TD]
[TD="class: xl65, width: 64, align: right"]$4.70[/TD]
[/TR]
[TR]
[TD="align: right"]2222[/TD]
[TD="class: xl65, align: right"]$4.70
[/TD]
[/TR]
[TR]
[TD="align: right"]4444[/TD]
[TD="class: xl65, align: right"]$4.40[/TD]
[/TR]
[TR]
[TD="align: right"]5555[/TD]
[TD="class: xl65, align: right"]$10.00[/TD]
[/TR]
</tbody>[/TABLE]

 

[buxtongt]

The first thing in the vlookup should be a single value, not a range, like you have it (A2:A6). Hope this helps.

 

[Trebor76]

Hi jschoon4,

See how this goes (assumes you're on excel 2007 or higher):

=IFERROR(VLOOKUP(A2,Sheet2!$A$1:$B$4,2,0),0)

Regards,

Robert

 

[dspel79082]

Hi Js, you do need to change the vlookup range, but the cause of the zeros is actually because you are using relative references to sheet 2 so when you copy it down the lookup range moves down til it is off the range. You need absolute references to your lookup range. Here's the corrected formula.

=IF(ISNA(
VLOOKUP(A2,Sheet2!$A$1:$B$4,2,0)),"0",VLOOKUP(A2,Sheet2!$A$1:$B$4,2,0))

 

[Robert Mika]

=IF(ISNA(
VLOOKUP(A2,Sheet2!$A$1:$B$4,2,0)),"0",VLOOKUP(A2,Sheet2!$A$1:$B$4,2,0))

There is no need to add "" to the 0 - unless you want this zero to be in Text format.

 

[jschoon4]

Thanks everyone that's great it works!

 

[Chita]

Hi everyone,
I try to follow the formula provided in this threads but it doesn't work for me

=IF(ISNA(VLOOKUP(J198,$H$242:$O$255,2,0)),"",VLOOKUP(J198,$H$242:$O$255,2,0))

It still shows "Zero" . I want it blank

Is any one can help please?

 

[Jonmo1]

You're missing a (Paren) and you left off the J in the 2nd vlookup.

=IF(ISNA(VLOOKUP(J198,$H$242:$O$255,2,0)),"",VLOOKUP(J198,$H$242:$O$255,2,0))