VLOOKUP Help

[djeerd]

I use the following VLOOKUP formula in Excell 2007.

=IF(B1=","",VLOOKUP(B1,[FJN]Invoice!A5:W1000,4))

When the cell in column #4 of FJN invoice is blank, a zero is displayed. How do I change the formula to get it to display a blank cell.

 

[T. Valko]

I use the following VLOOKUP formula in Excell 2007.

=IF(B1=","",VLOOKUP(B1,[FJN]Invoice!A5:W1000,4))

When the cell in column #4 of FJN invoice is blank, a zero is displayed. How do I change the formula to get it to display a blank cell.

Assuming the formula normally returns a numeric value...

=IF(B1="","",IF(VLOOKUP(B1,[FJN]Invoice!A5:W1000,4)="","",VLOOKUP(B1,[FJN]Invoice!A5:W1000,4)))

Or, it may be better to just use the formula as is but format the cell to not display the 0.

Use a custom cell format of:

General;-General;

The 0 will still be entered in the cell, you just won't see it.

If the formula normally returns a TEXT value...

=IF(B1="","",T(VLOOKUP(B1,[FJN]Invoice!A5:W1000,4)))

 

[djeerd]

Thank You. I used the custom format idea and it works well.

 

[T. Valko]

Thank You. I used the custom format idea and it works well.

You're welcome. Thanks for the feedback!

 

[grizz]

try this
=IF(ISNA(VLOOKUP(B1,[FJN]Invoice!s!$A$5:$W$1000,4,FALSE)=TRUE),"",VLOOKUP(B1,[FJN]Invoice!s!$A$5:$W$1000,4,FALSE))