VLookup - can I set my formula to fill in date if item isn't in ta

[Guest]

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

 

[T. Valko]

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

 

[Dave Peterson]

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

If you're using xl2007, you could use:

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

 

[Guest]

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]

Try this:

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

Biff

 

[Guest]

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$21698,0)),VLOOKUP(B10,$IK$138:$IT$21698,8,0),"Hdwr/Sftwr"))

Any thoughts? Thanks!

 

[T. Valko]

=IF(B10="","",IF(ISNUMBER(MATCH(B10,$IK$138:$IT$21698,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

 

[Guest]

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]

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