Formula to determine if a cell contains a value in a range

[zz4gz0]

I need help developing a formula that will look for one of 5 values in a cell and return the value it matches.

I have a worksheet with a column of descriptions. Within the description can be one of the following drive types: AWD, FWD, RWD, 2WD or 4WD. I want a formula that will look in the description cell and return drive type it finds.

Appreciate any help I can get.

[NBVC]

Try:

=CHOOSE(SUMPRODUCT((ISNUMBER(SEARCH({"AWD","FWD","RWD","2WD","4WD"},A2)))*(COLUMN($A$1:$E$1))),"AWD","FWD","RWD","2WD","4WD")

where A2 contains the description...formula can be copied down.

[arthurbr]

Have a look at the LOOKUP functions

[zz4gz0]

arthurbr
Thank you for taking the time to respond. I had looked at the lookup formulas along with multiple others. I have spend hours looking at this website and the Excel Bible trying to figure out on my own.
NBVC
THANK YOU THANK YOU THANK YOU. THAT WORKED WONDERFULLY.
NOW I JUST HAVE TO FIGURE OUT WHAT IT SAYS SO I UNDERSTAND HOW IT WORKS.

This was my first time posting to this forum and your wonderful.

[arthurbr]

A great source for XL information :http://www.cpearson.com/excel.htm

[daddylonglegs]

Here's a simpler way

=LOOKUP(2^15,SEARCH({"AWD","FWD"," RWD","2WD","4WD"},A2),{"AWD","FWD"," RWD","2WD","4WD"})

or put your 5 drive types in cells, e.g. D1:D5 and just use

=LOOKUP(2^15,SEARCH(D$1:D$5,A2),D$1:D$5)

[zz4gz0]

Both of those worked also.