ExcelBanter - Avoiding #N/A

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Avoiding #N/A (https://www.excelbanter.com/excel-discussion-misc-queries/255442-avoiding-n.html)

Hi, I use a spreadsheet for invoicing and have a product code entered into a
column when a product (in column C) is selected.

I also sometimes use this column to put a description in rather than pick a
product.

Can my formula be modified to avoid the #N/A appearing in the product code ?

Current formula is

=IF(C25<"",VLOOKUP(C25,Product_Data,2,FALSE)," ")

Thanks

Try

=IF(C25<"",IF(ISNA(VLOOKUP(C25,Product_Data,2,FAL SE)),"",VLOOKUP(C25,Product_Data,2,FALSE)),"
")

HTH

Bob

"gibbylinks" wrote in message
...
Hi, I use a spreadsheet for invoicing and have a product code entered into
a
column when a product (in column C) is selected.

I also sometimes use this column to put a description in rather than pick
a
product.

Can my formula be modified to avoid the #N/A appearing in the product code
?

Current formula is

=IF(C25<"",VLOOKUP(C25,Product_Data,2,FALSE)," ")

Thanks

Hi,
If your want to just remove the #N/A, then try to use Iserror function like
this,

=IF(ISERROR(IF(C25<"",VLOOKUP(C25,product_data,2, FALSE),"
")),"-",IF(C25<"",VLOOKUP(C25,product_data,2,FALSE) ," "))

It will return - in that respective sell.

Regards,

"gibbylinks" wrote:

Hi, I use a spreadsheet for invoicing and have a product code entered into a
column when a product (in column C) is selected.

I also sometimes use this column to put a description in rather than pick a
product.

Can my formula be modified to avoid the #N/A appearing in the product code ?

Current formula is

=IF(C25<"",VLOOKUP(C25,Product_Data,2,FALSE)," ")

Thanks

Wouldn't that be shorter ?
=IF(ISNA(VLOOKUP(C25,product_data,2,)),"-",VLOOKUP(C25,product_data,2,))
Micky

"Vital_ar" wrote:

Hi,
If your want to just remove the #N/A, then try to use Iserror function like
this,

=IF(ISERROR(IF(C25<"",VLOOKUP(C25,product_data,2, FALSE),"
")),"-",IF(C25<"",VLOOKUP(C25,product_data,2,FALSE) ," "))

It will return - in that respective sell.

Regards,

"gibbylinks" wrote:

Hi, I use a spreadsheet for invoicing and have a product code entered into a
column when a product (in column C) is selected.

I also sometimes use this column to put a description in rather than pick a
product.

Can my formula be modified to avoid the #N/A appearing in the product code ?

Current formula is

=IF(C25<"",VLOOKUP(C25,Product_Data,2,FALSE)," ")

Thanks

And better as ISERROR will ignore ANY error, nit just NA

Bob

"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
Wouldn't that be shorter ?
=IF(ISNA(VLOOKUP(C25,product_data,2,)),"-",VLOOKUP(C25,product_data,2,))
Micky

"Vital_ar" wrote:

Hi,
If your want to just remove the #N/A, then try to use Iserror function
like
this,

=IF(ISERROR(IF(C25<"",VLOOKUP(C25,product_data,2, FALSE),"
")),"-",IF(C25<"",VLOOKUP(C25,product_data,2,FALSE) ," "))

It will return - in that respective sell.

Regards,

"gibbylinks" wrote:

Hi, I use a spreadsheet for invoicing and have a product code entered
into a
column when a product (in column C) is selected.

I also sometimes use this column to put a description in rather than
pick a
product.

Can my formula be modified to avoid the #N/A appearing in the product
code ?

Current formula is

=IF(C25<"",VLOOKUP(C25,Product_Data,2,FALSE)," ")

Thanks

No doubt - but I, usually, try to focus on the questions - as is...
Micky

"Bob Phillips" wrote:

And better as ISERROR will ignore ANY error, nit just NA

Bob

"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
Wouldn't that be shorter ?
=IF(ISNA(VLOOKUP(C25,product_data,2,)),"-",VLOOKUP(C25,product_data,2,))
Micky

"Vital_ar" wrote:

Hi,
If your want to just remove the #N/A, then try to use Iserror function
like
this,

=IF(ISERROR(IF(C25<"",VLOOKUP(C25,product_data,2, FALSE),"
")),"-",IF(C25<"",VLOOKUP(C25,product_data,2,FALSE) ," "))

It will return - in that respective sell.

Regards,

"gibbylinks" wrote:

Hi, I use a spreadsheet for invoicing and have a product code entered
into a
column when a product (in column C) is selected.

I also sometimes use this column to put a description in rather than
pick a
product.

Can my formula be modified to avoid the #N/A appearing in the product
code ?

Current formula is

=IF(C25<"",VLOOKUP(C25,Product_Data,2,FALSE)," ")

Thanks

.

Then in that case I question why you joined in at all.

"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
No doubt - but I, usually, try to focus on the questions - as is...
Micky

"Bob Phillips" wrote:

And better as ISERROR will ignore ANY error, nit just NA

Bob

"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
Wouldn't that be shorter ?
=IF(ISNA(VLOOKUP(C25,product_data,2,)),"-",VLOOKUP(C25,product_data,2,))
Micky

"Vital_ar" wrote:

Hi,
If your want to just remove the #N/A, then try to use Iserror function
like
this,

=IF(ISERROR(IF(C25<"",VLOOKUP(C25,product_data,2, FALSE),"
")),"-",IF(C25<"",VLOOKUP(C25,product_data,2,FALSE) ," "))

It will return - in that respective sell.

Regards,

"gibbylinks" wrote:

Hi, I use a spreadsheet for invoicing and have a product code
entered
into a
column when a product (in column C) is selected.

I also sometimes use this column to put a description in rather than
pick a
product.

Can my formula be modified to avoid the #N/A appearing in the
product
code ?

Current formula is

=IF(C25<"",VLOOKUP(C25,Product_Data,2,FALSE)," ")

Thanks

.

If you'll look at the subject line of the question and/or read' again, its
content [quote]:
"Can my formula be modified to avoid the #N/A appearing in the product code ?"
This is what my reply referred to.
Does the above answer your question.
I'm sure that if you would see a suggestion to add 5 cells - like:
=A1+B1+C1+D1+E1 you would also comment that this can be shorten to:
=SUM(A1:E1)
Please take into consideration that my English is not good enough to argue
so, please, do not push me into such.
Micky

"Bob Phillips" wrote:

Then in that case I question why you joined in at all.

"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
No doubt - but I, usually, try to focus on the questions - as is...
Micky

"Bob Phillips" wrote:

And better as ISERROR will ignore ANY error, nit just NA

Bob

"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
Wouldn't that be shorter ?
=IF(ISNA(VLOOKUP(C25,product_data,2,)),"-",VLOOKUP(C25,product_data,2,))
Micky

"Vital_ar" wrote:

Hi,
If your want to just remove the #N/A, then try to use Iserror function
like
this,

=IF(ISERROR(IF(C25<"",VLOOKUP(C25,product_data,2, FALSE),"
")),"-",IF(C25<"",VLOOKUP(C25,product_data,2,FALSE) ," "))

It will return - in that respective sell.

Regards,

"gibbylinks" wrote:

Hi, I use a spreadsheet for invoicing and have a product code
entered
into a
column when a product (in column C) is selected.

I also sometimes use this column to put a description in rather than
pick a
product.

Can my formula be modified to avoid the #N/A appearing in the
product
code ?

Current formula is

=IF(C25<"",VLOOKUP(C25,Product_Data,2,FALSE)," ")

Thanks

.

.

I was supporting your original intervention within this thread by pointing
that no only is ISNA shorter then ISERROR, it is a better solution IMO. You
decided to take umbrage with that so nothing answers my question as I didn't
ask a question, and I don't care to get into arguments with people who get
stroppy with those who endorse their comments.

Bob

"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
If you'll look at the subject line of the question and/or read' again, its
content [quote]:
"Can my formula be modified to avoid the #N/A appearing in the product
code ?"
This is what my reply referred to.
Does the above answer your question.
I'm sure that if you would see a suggestion to add 5 cells - like:
=A1+B1+C1+D1+E1 you would also comment that this can be shorten to:
=SUM(A1:E1)
Please take into consideration that my English is not good enough to argue
so, please, do not push me into such.
Micky

"Bob Phillips" wrote:

Then in that case I question why you joined in at all.

"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
No doubt - but I, usually, try to focus on the questions - as is...
Micky

"Bob Phillips" wrote:

And better as ISERROR will ignore ANY error, nit just NA

Bob

"????? (????) ?????" <micky-a*at*tapuz.co.il wrote in message
...
Wouldn't that be shorter ?
=IF(ISNA(VLOOKUP(C25,product_data,2,)),"-",VLOOKUP(C25,product_data,2,))
Micky

"Vital_ar" wrote:

Hi,
If your want to just remove the #N/A, then try to use Iserror
function
like
this,

=IF(ISERROR(IF(C25<"",VLOOKUP(C25,product_data,2, FALSE),"
")),"-",IF(C25<"",VLOOKUP(C25,product_data,2,FALSE) ," "))

It will return - in that respective sell.

Regards,

"gibbylinks" wrote:

Hi, I use a spreadsheet for invoicing and have a product code
entered
into a
column when a product (in column C) is selected.

I also sometimes use this column to put a description in rather
than
pick a
product.

Can my formula be modified to avoid the #N/A appearing in the
product
code ?

Current formula is

=IF(C25<"",VLOOKUP(C25,Product_Data,2,FALSE)," ")

Thanks

.

.