Removing the 0 when no data is find ...

[Tartesos]

I'm using this formula:

=IF(ISNA(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B133),data!$X$4:$X$2488)),"",LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B133),data!$X$4:X$2488))

If the formula finds no data it show, sometimes, a "0".
I want to know if there is a way to stop that from happening. Anyone can help me??

Regards,

 

[Mike Blackman]

Hi,

Try something like this.....

IF(OR(ISNA(LOOKUP(...........)),LOOKUP(..........)=0),"",LOOKUP(......))

 

[Tartesos]

I tried that but there are 2 cells that are still getting the ''0" ....
Here is the formula with what you told to do... is this correct ??

=IF(OR(ISNA(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4),data!$X$4:$X$2488)=0)),"",LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B4),data!$X$4:X$2488))

Regards,

 

[Mike Blackman]

You can't put both arguments together like this;

=IF(OR(ISNA(LOOKUP(....))=0,"",....

You need to split them out and put your formula in twice.

IF(OR(ISNA(LOOKUP(...........)),LOOKUP(..........)=0),"",LOOKUP(......))

 

[Tartesos]

It does move the "0" but now when there is no data input on the B a #NA is on the X column

This the formula:

=IF(OR(ISNA(LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B731),data!$X$4:$X$2488)),LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B731),data!$X$4:$X$2488)=0),"",LOOKUP(9.99999999999999E+307,FIND(data!$B$4:$B$2488,imput!B731),data!$X$4:X$2488))

And is ok but the #NA is in the X column if I dont put any data on the B Column .. any ideas???

Thanks

 

[Tartesos]

Anyone have any ideas ???

Regards

 

[Tartesos]

anyone can help me with it??

Regards.

 

[Mike Blackman]

How do you mean there is an NA on Column X? Does the formula in question work fine but cause another calculation to work ok?

Is you FIND arguments not the wrong way around anyway?