I have quote spreadsheet in which sale rep can fill in a part number and the
description, price and type of item it is fills in automatically.

My question is, if they part number isn't in the vlookup table, I want a
default answer to fill in the type column instead of #N/A.

Is that possible, and if so, how would I do it?

Thanks

What default value would you like?

One way is to leave the cell blank if the lookup value isn't found:

=IF(ISNA(VLOOKUP(..........)),"",VLOOKUP(......... .))

Just replace the "" with whatever default value you want. Just remember that
a TEXT value needs to be enclosed in quotes: "text" and a numeric value
doesn't: 0.

Biff

"NANGO" wrote in message
...
I have quote spreadsheet in which sale rep can fill in a part number and
the
description, price and type of item it is fills in automatically.

My question is, if they part number isn't in the vlookup table, I want a
default answer to fill in the type column instead of #N/A.

Is that possible, and if so, how would I do it?

Thanks

=if(isna(vlookup(...)),"default answer",vlookup(....))

If you're using xl2007, you could use:

=iferror(vlookup(...),"Default answer")

NANGO wrote:

I have quote spreadsheet in which sale rep can fill in a part number and the
description, price and type of item it is fills in automatically.

My question is, if they part number isn't in the vlookup table, I want a
default answer to fill in the type column instead of #N/A.

Is that possible, and if so, how would I do it?

Thanks

--

Dave Peterson

Here is my formula:

=IF(B11="","",VLOOKUP(B11,$IO$138:$IT$20454,4,0))

and if B11 isn't a product in my lookup table, I want it to say "hardware"
instead of #NA.

"T. Valko" wrote:

What default value would you like?

One way is to leave the cell blank if the lookup value isn't found:

=IF(ISNA(VLOOKUP(..........)),"",VLOOKUP(......... .))

Just replace the "" with whatever default value you want. Just remember that
a TEXT value needs to be enclosed in quotes: "text" and a numeric value
doesn't: 0.

Biff

"NANGO" wrote in message
...
I have quote spreadsheet in which sale rep can fill in a part number and
the
description, price and type of item it is fills in automatically.

My question is, if they part number isn't in the vlookup table, I want a
default answer to fill in the type column instead of #N/A.

Is that possible, and if so, how would I do it?

Thanks

Try this:

=IF(B11="","",IF(ISNUMBER(MATCH(B11,$IO$138:$IO$20 454,0)),VLOOKUP(B11,$IO$138:$IT$20454,4,0),"Hardwa re"))

Biff

"NANGO" wrote in message
...
Here is my formula:

=IF(B11="","",VLOOKUP(B11,$IO$138:$IT$20454,4,0))

and if B11 isn't a product in my lookup table, I want it to say "hardware"
instead of #NA.

"T. Valko" wrote:

What default value would you like?

One way is to leave the cell blank if the lookup value isn't found:

=IF(ISNA(VLOOKUP(..........)),"",VLOOKUP(......... .))

Just replace the "" with whatever default value you want. Just remember
that
a TEXT value needs to be enclosed in quotes: "text" and a numeric value
doesn't: 0.

Biff

"NANGO" wrote in message
...
I have quote spreadsheet in which sale rep can fill in a part number and
the
description, price and type of item it is fills in automatically.

My question is, if they part number isn't in the vlookup table, I want
a
default answer to fill in the type column instead of #N/A.

Is that possible, and if so, how would I do it?

Thanks

I must be doing something wrong, because even the product is in my list and
should fill in the type of the item it still puts "hardware" as default, when
some of them are not software.

I'm trying to get this to put "hardware" as default when the part number
they enter is not in my vlookup table. Does that make sense?

Here's the formula I tried (it's changed because my columns & rows have
expanded since my last e-mail).

=IF(B10="","",IF(ISNUMBER(MATCH(B10,$IK$138:$IT$21 698,0)),VLOOKUP(B10,$IK$138:$IT$21698,8,0),"Hdwr/Sftwr"))

Any thoughts? Thanks!

"T. Valko" wrote:

Try this:

=IF(B11="","",IF(ISNUMBER(MATCH(B11,$IO$138:$IO$20 454,0)),VLOOKUP(B11,$IO$138:$IT$20454,4,0),"Hardwa re"))

Biff

"NANGO" wrote in message
...
Here is my formula:

=IF(B11="","",VLOOKUP(B11,$IO$138:$IT$20454,4,0))

and if B11 isn't a product in my lookup table, I want it to say "hardware"
instead of #NA.

"T. Valko" wrote:

What default value would you like?

One way is to leave the cell blank if the lookup value isn't found:

=IF(ISNA(VLOOKUP(..........)),"",VLOOKUP(......... .))

Just replace the "" with whatever default value you want. Just remember
that
a TEXT value needs to be enclosed in quotes: "text" and a numeric value
doesn't: 0.

Biff

"NANGO" wrote in message
...
I have quote spreadsheet in which sale rep can fill in a part number and
the
description, price and type of item it is fills in automatically.

My question is, if they part number isn't in the vlookup table, I want
a
default answer to fill in the type column instead of #N/A.

Is that possible, and if so, how would I do it?

Thanks

=IF(B10="","",IF(ISNUMBER(MATCH(B10,$IK$138:$IT$21 698,0)),VLOOKUP(B10,$IK$138:$IT$21698,8,0),"Hdwr/Sftwr"))

Any thoughts?

The 2nd argument in MATCH *MUST* be a 1 dimensional array:

MATCH(B10,$IK$138:$IT$21698,0)

Change it to:

MATCH(B10,$IK$138:$IK$21698,0)

Biff

"NANGO" wrote in message
...
I must be doing something wrong, because even the product is in my list and
should fill in the type of the item it still puts "hardware" as default,
when
some of them are not software.

I'm trying to get this to put "hardware" as default when the part number
they enter is not in my vlookup table. Does that make sense?

Here's the formula I tried (it's changed because my columns & rows have
expanded since my last e-mail).

=IF(B10="","",IF(ISNUMBER(MATCH(B10,$IK$138:$IT$21 698,0)),VLOOKUP(B10,$IK$138:$IT$21698,8,0),"Hdwr/Sftwr"))

Any thoughts? Thanks!

"T. Valko" wrote:

Try this:

=IF(B11="","",IF(ISNUMBER(MATCH(B11,$IO$138:$IO$20 454,0)),VLOOKUP(B11,$IO$138:$IT$20454,4,0),"Hardwa re"))

Biff

"NANGO" wrote in message
...
Here is my formula:

=IF(B11="","",VLOOKUP(B11,$IO$138:$IT$20454,4,0))

and if B11 isn't a product in my lookup table, I want it to say
"hardware"
instead of #NA.

"T. Valko" wrote:

What default value would you like?

One way is to leave the cell blank if the lookup value isn't found:

=IF(ISNA(VLOOKUP(..........)),"",VLOOKUP(......... .))

Just replace the "" with whatever default value you want. Just
remember
that
a TEXT value needs to be enclosed in quotes: "text" and a numeric
value
doesn't: 0.

Biff

"NANGO" wrote in message
...
I have quote spreadsheet in which sale rep can fill in a part number
and
the
description, price and type of item it is fills in automatically.

My question is, if they part number isn't in the vlookup table, I
want
a
default answer to fill in the type column instead of #N/A.

Is that possible, and if so, how would I do it?

Thanks

I'm still doing something wrong? No matter what I enter, I get "hardware"
even if column C is different.

Here's an example vlookup table:

Col A = Part # Col B = Description Col C = Product Type
Row 1
Row 2
Row 3

If I enter a valid part that's in row 1, 2 or 3, I want the product type
from row C, whether it's "hardware", "software", or "maintenance".

Then, if I enter a part number that isn't in row 1, 2 or 3, that's when I
want it to default to "hardware".






"T. Valko" wrote:

=IF(B10="","",IF(ISNUMBER(MATCH(B10,$IK$138:$IT$21 698,0)),VLOOKUP(B10,$IK$138:$IT$21698,8,0),"Hdwr/Sftwr"))

Any thoughts?

The 2nd argument in MATCH *MUST* be a 1 dimensional array:

MATCH(B10,$IK$138:$IT$21698,0)

Change it to:

MATCH(B10,$IK$138:$IK$21698,0)

Biff

"NANGO" wrote in message
...
I must be doing something wrong, because even the product is in my list and
should fill in the type of the item it still puts "hardware" as default,
when
some of them are not software.

I'm trying to get this to put "hardware" as default when the part number
they enter is not in my vlookup table. Does that make sense?

Here's the formula I tried (it's changed because my columns & rows have
expanded since my last e-mail).

=IF(B10="","",IF(ISNUMBER(MATCH(B10,$IK$138:$IT$21 698,0)),VLOOKUP(B10,$IK$138:$IT$21698,8,0),"Hdwr/Sftwr"))

Any thoughts? Thanks!

"T. Valko" wrote:

Try this:

=IF(B11="","",IF(ISNUMBER(MATCH(B11,$IO$138:$IO$20 454,0)),VLOOKUP(B11,$IO$138:$IT$20454,4,0),"Hardwa re"))

Biff

"NANGO" wrote in message
...
Here is my formula:

=IF(B11="","",VLOOKUP(B11,$IO$138:$IT$20454,4,0))

and if B11 isn't a product in my lookup table, I want it to say
"hardware"
instead of #NA.

"T. Valko" wrote:

What default value would you like?

One way is to leave the cell blank if the lookup value isn't found:

=IF(ISNA(VLOOKUP(..........)),"",VLOOKUP(......... .))

Just replace the "" with whatever default value you want. Just
remember
that
a TEXT value needs to be enclosed in quotes: "text" and a numeric
value
doesn't: 0.

Biff

"NANGO" wrote in message
...
I have quote spreadsheet in which sale rep can fill in a part number
and
the
description, price and type of item it is fills in automatically.

My question is, if they part number isn't in the vlookup table, I
want
a
default answer to fill in the type column instead of #N/A.

Is that possible, and if so, how would I do it?

Thanks

At this point, there's nothing wrong with that formula and it should work.
If it doesn't then I suspect there's something wrong with the data.
Incompatible data types? Unseen characters like leading and/or trailing
spaces in the lookup_value cell and/or the lookup_array (first column of the
lookup_table)

I'm absolutely sure that there's nothing wrong with the formula (if you've
corrected it the way I explained).

Biff

"NANGO" wrote in message
...
I'm still doing something wrong? No matter what I enter, I get "hardware"
even if column C is different.

Here's an example vlookup table:

Col A = Part # Col B = Description Col C = Product Type
Row 1
Row 2
Row 3

If I enter a valid part that's in row 1, 2 or 3, I want the product type
from row C, whether it's "hardware", "software", or "maintenance".

Then, if I enter a part number that isn't in row 1, 2 or 3, that's when I
want it to default to "hardware".






"T. Valko" wrote:

=IF(B10="","",IF(ISNUMBER(MATCH(B10,$IK$138:$IT$21 698,0)),VLOOKUP(B10,$IK$138:$IT$21698,8,0),"Hdwr/Sftwr"))

Any thoughts?

The 2nd argument in MATCH *MUST* be a 1 dimensional array:

MATCH(B10,$IK$138:$IT$21698,0)

Change it to:

MATCH(B10,$IK$138:$IK$21698,0)

Biff

"NANGO" wrote in message
...
I must be doing something wrong, because even the product is in my list
and
should fill in the type of the item it still puts "hardware" as
default,
when
some of them are not software.

I'm trying to get this to put "hardware" as default when the part
number
they enter is not in my vlookup table. Does that make sense?

Here's the formula I tried (it's changed because my columns & rows have
expanded since my last e-mail).

=IF(B10="","",IF(ISNUMBER(MATCH(B10,$IK$138:$IT$21 698,0)),VLOOKUP(B10,$IK$138:$IT$21698,8,0),"Hdwr/Sftwr"))

Any thoughts? Thanks!

"T. Valko" wrote:

Try this:

=IF(B11="","",IF(ISNUMBER(MATCH(B11,$IO$138:$IO$20 454,0)),VLOOKUP(B11,$IO$138:$IT$20454,4,0),"Hardwa re"))

Biff

"NANGO" wrote in message
...
Here is my formula:

=IF(B11="","",VLOOKUP(B11,$IO$138:$IT$20454,4,0))

and if B11 isn't a product in my lookup table, I want it to say
"hardware"
instead of #NA.

"T. Valko" wrote:

What default value would you like?

One way is to leave the cell blank if the lookup value isn't found:

=IF(ISNA(VLOOKUP(..........)),"",VLOOKUP(......... .))

Just replace the "" with whatever default value you want. Just
remember
that
a TEXT value needs to be enclosed in quotes: "text" and a numeric
value
doesn't: 0.

Biff

"NANGO" wrote in message
...
I have quote spreadsheet in which sale rep can fill in a part
number
and
the
description, price and type of item it is fills in automatically.

My question is, if they part number isn't in the vlookup table, I
want
a
default answer to fill in the type column instead of #N/A.

Is that possible, and if so, how would I do it?

Thanks