Finding multiple values to return multiple values

I was hoping to be able to use ELSEIF statements in an excel function, but I guess you can't?

But basicallly, I have a bill of materials with a description column. I want to search that column for various words (ie. wheel, screw, spacer, shelf, etc) and return a value into another new column depending on that value (wheel inputs wheel, screw inputs hardware, spacer inputs hardware, shelf inputs shelf).

How Excel shows you how to search will only return one value because I can't use an else statement:

Please Login or Register  to view this content.

Here's probably a harder issue - there needs to be a hierarchy to the code so that "shelf screw" would return screw.

There's about 30 different categories (return values) and about 3-4 words (per category) that could be located in the description that should return that value.

ie. SPACER, SCREW, NUT, BOLT will all return Hardware

I have a feeling I might be in the wrong forum. \1 (apparently image tags are disabled...lol)

Thanks for any help!

BTW, I searched for a similar situation in this forum for about 30-45min before posting what I figured might be a constantly asked question. And I found this website using google to look for tutorials and help. So I did some digging around other sites before just bugging you guys for some suggestions. Sorry for being another random new guy looking for help

Originally Posted by NBVC

You're image link doesn't work for me....

Really? Maybe tinyurl gets work filtered some places. It's just a disturbed smile from another msg board I use. heh.

BTW, I was born in Burlington, eh

Originally Posted by Portuga

You could probably use a vlookup.

In one sheet you would have the list of words and categories associated with them and the use a vlookup formula like this:

=vlookup("*"&A1&"*",List!$A$1:$B$1000,2,0) dragged down.

I can't get that to do anything but find the first category written down and then N/A the rest of them. Maybe I just need to understand what lookup_value you're using means?

BTW, in Sheet 2, I'm organizing them like:

COL1 COL2
SCREW Hardware
SPACER Hardware
NUT Hardware
SHELF Shelf

In Sheet1 it'll only look like
PART1 Hardware
PART2 #N/A
PART3 #N/A


...I'd take screenshots but I can't put the BOMs online. :\

Also, I have an easier question...

The if statement that looks for ASH within the description is returning WASHER too. How do I tell it to look for ASH without any letters on either side?

Thanks

Aha! That's how I combine the if statements! :D After doing some of them like that I'm not sure I like it that way though because of some of the issues with double categorizing. Maybe I can make the rules more specific and use it. Thanks for this!

And unfortunately the part numbers, although unique, are not smart. So trying to use them to develop a category structure won't work. :\ The second that you think you see a pattern I could find you another list of them where someone screwed it up and used the wrong first digit. lol.

not sure if you still need this but here is an option =IF(A2="SCREWS","HARDWARE",IF(A2="NUTS","HARDWARE",IF(A2="BOLTS","HARDWARE",IF(A2="BRACKETS","HARDWARE",IF(A2="NAILS","HARDWARE",IF(A2="DESK","FURNITURE",IF(A2="CHAIR","FURNITURE",IF(A2="SINK","PLUMBING",IF(A2="FAUCET","PLUMBING")))))))))
just added the other last four as examples of multiple departments
hope this help can do something similar with vlookup but it uses more resources, this is often a quicker solution