How do get the LOOKUP function to not read empty cells as zeros

[LGriffin]

I am trying to reverse code 0s and 1s with =LOOKUP (reference cell,
{0,1},{1,0}). When the reference cell is empty, I'd like the function to
also return an empty cell.

 

[Shane Devenshire]

Hi,

why are you using lookup?

=IF(A1=0,1,IF(A1=1,0,""))

 

[Mike H]

Maybe

=IF(ISBLANK(A1),"",LOOKUP(A1,{0,1},{1,0}))

Mike

 

[JoeU2004]

I am trying to reverse code 0s and 1s with =LOOKUP (reference cell,
{0,1},{1,0}). When the reference cell is empty, I'd like the function to
also return an empty cell.

=if(A1="","",if(A1,0,1))

treats all non-zero A1 as 1, changing them to 0.

A formula cannot "return an empty cell". It can only make a cell __appear__
blank by returning a null string.

For that reason, use A1="", not ISBLANK(A1), to test for a blank cell.
ISBLANK() returns true only when the cell is truly empty -- that is, no
formula and no value. A1="" returns true in that case as well as when the
value in A1 is the null string, such as the result of IF() expression like
the above.

 

[JoeU2004]

=IF(A1=0,1,IF(A1=1,0,""))

Returns 1 when A1 is empty. But the OP wrote: "When the reference cell is
empty, I'd like the function to also return an empty cell".

Of course, a formula cannot "return an empty cell". But it can return a
null string.


----- original message -----

 

[Teethless mama]

Try one of these formula:

=--NOT(A1)
or
=--(A1<>1)

 

[Teethless mama]

Another one...

=DELTA(A1)

 

[Teethless mama]

.....and another

=1-A1

 

[Harlan Grove]

....and another

=1-A1

....

Expand for blank cells to

=IF(COUNT(A1),1-A1,"")

 

[JoeU2004]

Improvement....

=if(A1="","",if(A1,0,1))

=if(A1="", "", --NOT(A1))


----- original message -----