How do I eliminate "false" from showing in a cell?

[Guest]

 

[Bob Phillips]

Show us your formula, and tell us what you want in its stead.

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

 

[JE McGimpsey]

How is FALSE getting there?

Assuming it's due to an IF() statement, put what you want to appear in
the third argument:

=IF(A1<B1,"It's true","It's not true")

If you want the cell to *appear* blank:

=IF(A1<B1,"It's true","")




thistooshallpass <[email protected]> wrote:

 

[Guest]

Thanks for the response, however, this is the case.

I am using the vlookup formula, =vlookup(B9,Q12:R41,2,"") which produces
"#N/A" when the lookup value cell is blank. So I decided to use an if
formula, =if(B9<0,"0,if(B9>0,vlookup(B9,Q12:r41,2,0))) thinking that I could
get rid of the #N/A, which I did but inherited 'false' in the cell.

 

[Guest]

Thanks for the response, however, this is the case.

I am using the vlookup formula, =vlookup(B9,Q12:R41,2,"") which produces
"#N/A" when the lookup value cell is blank. So I decided to use an if
formula, =if(B9<0,"0,if(B9>0,vlookup(B9,Q12:r41,2,0))) thinking that I could
get rid of the #N/A, which I did but inherited 'false' in the cell.

 

[Pete_UK]

A better way of avoiding the #NA error is to use ISNA, as follows:

=IF(ISNA(VLOOKUP(B9,Q12:R41,2,0),"",VLOOKUP(B9,Q12:R41,2,0))

This will return a blank (i.e. "" in the middle of the formula) instead
of #NA - you might like this to be zero (i.e. 0 without quotes in the
middle of the formula).

Hope this helps.

Pete

 

[Bob Phillips]

Two ways,

=IF(B9="","",vlookup(B9,Q12:R41,2,False))

or more stable as it will also handle any invalid value

=IF(ISNA(vlookup(B9,Q12:R41,2,False) ,"",vlookup(B9,Q12:R41,2,False) )

--
HTH

Bob Phillips

(replace somewhere in email address with googlemail if mailing direct)

 

[Guest]

Thanks for the response, however, i get an error message when i attempt to
input this formula. The "" are hightlighted from the error message
indicating that the error is within the portion of the formula. I have also
attempted to replace the "" with simply an 0 and other inputs but I still
receive the error message. Am I missing something with the input?

 

[Guest]

Thanks. The first way worked, however, I still am not able to get pass the
error message which highlights the "" portion of the formula.

 

[JE McGimpsey]

One way:

=IF(ISNA(MATCH(B9,Q12:Q41,FALSE)),"",VLOOKUP(B9,Q12:R41,2,FALSE))

 

[Pete_UK]

Just check what you have typed in more thoroughly - have you missed out
any brackets (there are 2 at the end of the formula), have you included
all of the commas, have you included the colons etc?

If you can't spot anything wrong, then copy/paste your formula directly
to your reply (put an apostrophe in front of the = sign in Excel.

Pete

 

[Bob Phillips]

My bad

=IF(ISNA(VLOOKUP(B9,Q12:R41,2,FALSE)),"",VLOOKUP(B9,Q12:R41,2,FALSE))

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

 

[Pete_UK]

Sorry, there is a missing bracket from my formula - it should be:

=IF(ISNA(VLOOKUP(B9,Q12:R41,2,0)),"",VLOOKUP(B9,Q12:R41,2,0))

This should correct it now (thanks to Bob's earlier response!)

Pete

 

[Guest]

Thanks.

One way:

=IF(ISNA(MATCH(B9,Q12:Q41,FALSE)),"",VLOOKUP(B9,Q12:R41,2,FALSE))