Using =IF(ISNA function with vlookup

jschoon4

New Member
Joined
Dec 16, 2010
Messages
35
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
markupFinal PricePremier Price
1111
$4.703.754.88$4.70
2222$4.703.754.88$4.70
33331$4.403.754.550 0
4444
$4.403.754.550 0
5555$10.003.7510.380 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
Joined
Oct 5, 2011
Messages
709
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
Joined
Jul 23, 2007
Messages
4,467
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
Joined
Sep 29, 2012
Messages
125
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
Joined
Jun 29, 2009
Messages
7,256
=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.
 

Chita

New Member
Joined
Apr 20, 2016
Messages
7
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:

Forum statistics

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

Some videos you may like

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...
Top