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