vlookup returning 0 value when told not to

[Oly Steel Man]

I've had this problem for sometime and while its not hindering me right now it does create a bit of visual confusion on the screen and when trying to distribute a report to co-workers. I've have a sheet that I use the vlookup function on and when told to display nothing in the event of an isna value it will display a 0 instead. I've attached a copy of the sheet so you can dissect as necessary. Again it references properly when data is present but not when there is an absence of data. Use column D as a reference for the error, cell D14 is the first instance of this error. I've tried different formats for the cells as well.

[davegugg]

You don't have any NA on the Packet Schedule sheet, so the ISNA function is always going to return False. Instead test for blank like this:

=IF(VLOOKUP($A14,'Packet Schedule'!$A$1:$C$498,3,FALSE)="","",VLOOKUP($A14,'Packet Schedule'!$A$1:$C$498,3,FALSE))

[Oly Steel Man]

You don't have any NA on the Packet Schedule sheet, so the ISNA function is always going to return False. Instead test for blank like this:

=IF(VLOOKUP($A14,'Packet Schedule'!$A$1:$C$498,3,FALSE)="","",VLOOKUP($A14,'Packet Schedule'!$A$1:$C$498,3,FALSE))

This would work so long as I have a value in the lookup column (A). It does return an N/A value in the other cells where there is no lookup value. That's why I specified the isna portion. I suppose I'll have to just auto fill the formula if I have values in column A and leave the others blank until I have data.

[Oly Steel Man]

=IF(IF(ISNA(VLOOKUP($A14,'Packet Schedule'!$A$1:$F$1000,3,FALSE)),"0",VLOOKUP($A14,'Packet Schedule'!$A$1:$F$1000,3,FALSE))="0","",VLOOKUP($A14,'Packet Schedule'!$A$1:$F$1000,3,FALSE))

Crude but this did the trick. Any help on cleaning it up?

[Cutter]

Since none of the values in the searched column are less than 4 characters would it be safe to assume that will always be the case?

If so you could use:

=if(LEN(VLOOKUP($A14,'Packet Schedule'!$A$1:$C$498,3,FALSE)<2,"",VLOOKUP($A14,'Packet Schedule'!$A$1:$C$498,3,FALSE))

That will take care of 0's or blanks

[daddylonglegs]

You could just use your original formula with &"" appended to the second VLOOKUP, that will suppress the zeroes, i.e.

=IF(ISNA(VLOOKUP($A2,'Packet Schedule'!$A$2:$E$599,5,FALSE)),"",VLOOKUP($A2,'Packet Schedule'!$A$2:$E$599,5,FALSE)&"")

[ChemistB]

Your zeros are not coming from the ISNA portion of your equation but from the vlookup when you find something in column A (the id is on your packet schedule) but there's nothing to return in column 3. You'll need to nest a second IF statement to return "" if VLOOKUP returns 0 or set up your options to not show zeros. Hope that helps.

[ron2k_1]

The prob is not in the formula, it is because on you packet schedule for items 759142 and 759144 and many others you have nothing under column 3 (or C) as instructed by your vlookup formula, so you need to enter something in that column so that it returns something else other than 0, or an addtional IF argument.

EDIT: Hey! I'm late for the party

[Cutter]

If you highlight the vlookup portion of your formula and hit F9 you'll see that it equates to 0 so the ISNA will not kick in. To allow for a 0 value use:

=IF(OR(ISNA(VLOOKUP($A14,'Packet Schedule'!$A$1:$C$498,3,FALSE)),VLOOKUP($A14,'Packet Schedule'!$A$1:$C$498,3,FALSE)=0),"",VLOOKUP($A14,'Packet Schedule'!$A$1:$C$498,3,FALSE))

[davegugg]

Look at Cutter's response, he fixed my oversight on that issue.