VLOOKUP
 

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

VLOOKUP
 

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

If(formula = 0, "", formula)
--
HTH...

Jim Thomlinson


"FP Novice" wrote:

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

VLOOKUP
 

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










"FP Novice" wrote:

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

VLOOKUP
 

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

"FP Novice" wrote:

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

VLOOKUP
 

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



"xlmate" wrote:

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










"FP Novice" wrote:

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

VLOOKUP
 

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










"FP Novice" wrote:

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

VLOOKUP
 

You can also format the cell to supress the zero
#,##0.00;(#,##0.00);;
"FP Novice" wrote:

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



"xlmate" wrote:

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










"FP Novice" wrote:

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

VLOOKUP
 

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

"Sheeloo" wrote:

You can also format the cell to supress the zero
#,##0.00;(#,##0.00);;
"FP Novice" wrote:

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



"xlmate" wrote:

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










"FP Novice" wrote:

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

VLOOKUP
 

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?

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.

VLOOKUP
 

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

On Feb 24, 6:24*pm, FP Novice
wrote:
=IF(ISERROR(VLOOKUP(E6&"-"&F6,Table!A:L,7,FALSE)),"",VLOOKUP(E6&"-"&F6,Tabl*e!A:L,7,FALSE))



"xlmate" wrote:
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

"FP Novice" wrote:

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- Hide quoted text -

- Show quoted text -

VLOOKUP
 

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.