VLOOKUP

[FP Novice]

I am using some lookup functions in multiple cells of a spreadsheet.
Depending on the search a cell may actually have a null value. When a null
value is returned the cell is populated with a '0'. My question is how do I
make it so a null value is simply an empty cell?

Thank you,
Todd

 

[Jim Thomlinson]

determine if your formula will return a zero using an if statement...

If(formula = 0, "", formula)

 

[xlmate]

would you show your formula

--
Hope this is helpful

Appreciate that you provide your feedback by clicking the Yes button below
if this post have helped you.


Thank You

cheers, francis

 

[Sheeloo]

One way is to use
=IF(your formula = 0,"",yourformula)

 

[FP Novice]

=IF(ISERROR(VLOOKUP(E6&"-"&F6,Table!A:L,7,FALSE)),"",VLOOKUP(E6&"-"&F6,Table!A:L,7,FALSE))

 

[xlmate]

try this if your formula is Vlookup and you want to return a blank instead of
a 0

=IF(VLOOKUP(E2,A2:B12,2,0)=0,"",VLOOKUP(E2,A2:BC12,2,0))

Adjust the range to yours

--
Hope this is helpful

Appreciate that you provide your feedback by clicking the Yes button below
if this post have helped you.


Thank You

cheers, francis

 

[Sheeloo]

You can also format the cell to supress the zero
#,##0.00;(#,##0.00);;

 

[FP Novice]

That did supress the zero Sheeloo but it also rendered the IF formula
ineffective.

 

[MyVeryOwnSelf]

I am using some lookup functions in multiple cells of a spreadsheet.

One way is to use
=IF(your formula = 0,"",yourformula)

Maybe I'm missing something, but isn't it possible that zero is a valid
result of "formula" that needs to be treated differently from a null value?
For example, one might be computing an AVERAGE() over a range of these
results.

I'd suggest
=IF(your_formula="","",your_formula)
instead.

 

[Pete_UK]

This is a bit messy, but you do need to apply the test twice:

=IF(ISNA(MATCH(E6&"-"&F6,Table!A:A,0)),"",IF(VLOOKUP(E6&"-"&F6,Tabl­e!
A:L,7,0)="","",VLOOKUP(E6&"-"&F6,Tabl­e!A:L,7,0)))

I've used MATCH instead of the first VLOOKUP - slightly faster.

Hope this helps.

Pete

 

[MyVeryOwnSelf]

This is a bit messy, but you do need to apply the test twice:

=IF(ISNA(MATCH(E6&"-"&F6,Table!A:A,0)),"",IF(VLOOKUP(E6&"-"&F6,Tabl­e!
A:L,7,0)="","",VLOOKUP(E6&"-"&F6,Tabl­e!A:L,7,0)))

Slightly less messy:
=IF(ISERROR(1/LEN(your_formula)),"", your_formula)

Explanation:

If your_formula results in a lookup error (or other error), then so does
LEN(your_formula) and 1/LEN(your_formula).

OTOH, if your_formula results in an empty string, then 1/LEN(your_formula)
is a divide-by-zero error.