# Using =IF(ISNA function with vlookup

#### jschoon4

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

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

#### Trebor76

##### Well-known Member
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

##### Board Regular
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

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

##### New Member
Thanks everyone that's great it works!

#### Chita

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

Last edited:

#### Jonmo1

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

1,081,575
Messages
5,359,715
Members
400,545
Latest member
Damntheman30

### This Week's Hot Topics

• VBA (Userform)
Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
• List box that changes fill color
Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
• Remove duplicates and retain one. Cross-linked cases
Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
• VBA Copy and Paste With Duplicates
Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
• Macro
is it possible for a macro to run if the active cell value is different to the value above it
• IF DATE and TIME
I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...