How to get rid of extracted cells displaying "0"

[ACM2]

At the moment, when I am using VLOOKUP to extract data from another worksheet, when it is extracting a blank cell, instead of displaying it as empty on the local sheet, it displays a "0" in the cell. Here's my formula. I've tried many combos, but have given up repeatedly trying. Thanks.

=IF(ISNA(VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)),"",VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0))

[Richard Buttrey]

What's in B21 and what's in column L for a matching B21 value?

[npamcpp]

If a successful lookup only finds an empty cell, then it will return 0. Even =A1 will return 0 if A1 is empty.

There are several approaches to handle this, for example format the cells not to display zero values with custom format

0.00,-0.00;;

or similar, depending on the format you need.

Or you wrap your formula in an IF statement that checks if the returned cell is empty

=if(YourFormula="","",YourFormula)

Or, in your specific case, since you already use an IF statement, add an OR() to the condition

=IF(or(ISNA(VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)),VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)=""),"",VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0))

[ACM2]

If a successful lookup only finds an empty cell, then it will return 0. Even =A1 will return 0 if A1 is empty.

There are several approaches to handle this, for example format the cells not to display zero values with custom format

0.00,-0.00;;

or similar, depending on the format you need.

Or you wrap your formula in an IF statement that checks if the returned cell is empty

=if(YourFormula="","",YourFormula)

Or, in your specific case, since you already use an IF statement, add an OR() to the condition

=IF(or(ISNA(VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)),VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)=""),"",VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0))

That works fine, however, it is displaying "N/A" in the cells where there are no records. How can I remove these N/As so the cells remain blank?

[abousetta]

Try:

=IF(ISNA(YourFormulaHere,"",YourFormulaHere)

This will put blanks where no records are found.

[ACM2]

Try:

=IF(ISNA(YourFormulaHere,"",YourFormulaHere)

This will put blanks where no records are found.

Yes, but I am still getting error messages, probably not putting a parenthesis in the right place or something.

=IF(OR(ISNA(VLOOKUP($B107,'Season Results History (Hidden)'!$B:$Y,8,0)),VLOOKUP($B107,'Season Results History (Hidden)'!$B:$Y,8,0)=""),"",VLOOKUP($B107,'Season Results History (Hidden)'!$B:$Y,8,0))

[Richard Buttrey]

Probably time to upload your workbook.

When you get a moment would you also extend your profile to indicate which Excel versions you have. Knowing this can be useful since later versions of Excel have functionality that does not exist in earlier versions

[npamcpp]

Try:

=IF(ISNA(YourFormulaHere,"",YourFormulaHere)

This will put blanks where no records are found.

The closing bracket for the ISNA() is missing. It should be

=IF(ISNA(YourFormulaHere),"",YourFormulaHere)

ACM2, your question is about avoiding the return of Zero from a formula. A zero as the result is not an error and can be handled by the techniques described above. If the formula returns #N/A, then it's an error and needs to be handled differently. You can either use a formula like the one above, or in Excel 2007 and later use

=IFError(YourFormulaHere,"")

or you can prevent errors occurring in the first place by good data management. E.g. ensure that lookup value and the data in the lookup range have the same data type, no leading or trailing blanks, and more.

As has been mentioned above, this would be easier to demonstrate with a workbook.

Here is a good article on why Vlookup() can fail: Six Reasons Why Your VLOOKUP or HLOOKUP Formula Does Not Work

[abousetta]

If the first formula worked fine then this should work also:

=If(ISNA(IF(or(ISNA(VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)),VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)=""),"",VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0))),"",IF(or(ISNA(VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)),VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)=""),"",VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)))

[ACM2]

If the first formula worked fine then this should work also:

=If(ISNA(IF(or(ISNA(VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)),VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)=""),"",VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0))),"",IF(or(ISNA(VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)),VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)=""),"",VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)))

That worked fine. Thanks.

[npamcpp]

If the first formula worked fine then this should work also:

=If(ISNA(IF(or(ISNA(VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)),VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)=""),"",VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0))),"",IF(or(ISNA(VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)),VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)=""),"",VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0)))

If avoiding both #N/A and 0 as the result is the goal, then this is a shorter way:

=IF(ISNA(VLOOKUP(B21,'Season Results History (Hidden)'!B:Y,11,0)),"",IF(VLOOKUP(Sheet1!B21,'Season Results History (Hidden)'!B:Y,11,0)=0,"",VLOOKUP(Sheet1!B21,'Season Results History (Hidden)'!B:Y,11,0)))

[jason.b75]

As suggested earlier, it's also a good idea to have your excel version indicated in your profile. Also a brief description of your sheet helps, more information leads to more accurate answers.

One possible answer to your question would be

=T(IFERROR(VLOOKUP($B21,'Season Results History (Hidden)'!$B:$Y,11,0),""))

but that would be of no use if you use excel 2003, or if the lookup results are numeric.