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

Mfg #	cost	markup	Final Price	Premier Price
1111	$4.70	3.75	4.88	$4.70
2222	$4.70	3.75	4.88	$4.70
33331	$4.40	3.75	4.55	0 0
4444	$4.40	3.75	4.55	0 0
5555	$10.00	3.75	10.38	0 0

<colgroup><col><col><col><col><col></colgroup><tbody>
</tbody>

Sheet 2
A B

1111	$4.70
2222	$4.70
4444	$4.40
5555	$10.00

<colgroup><col style="width:48pt" span="2" width="64"> </colgroup><tbody>
</tbody>

 

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