Descriptions based on cell value

**MattOneDime** · 03-09-2007, 03:14 PM

Hi everyone..

I have a worksheet(Descriptions) with 5 descriptions in 5 seperate cells (call them A1 through A5). On my other worksheet (List) I have about 800 part numbers for various applications. The part numbers are like this: 8001-BASIC, 8003-SS, 8001-FL, and so on.. The BASIC, SS, FL, PL, & SRC are what determines which description applies.

So my question is how can I make colum C on my List worksheet display the appropriate description based on the contents of the part number column?

Side note: If it helps I have already figured out how to extract the -BASIC, -SS, and so on from the part number into a column that contains only "BASIC" "SS" ect. for each part number.

Thanks!!!
Matt

**mrice** · 03-09-2007, 03:16 PM

I suggest that you have a look at the HELP for the VLOOKUP function. This should tell you what you need to know.

**MattOneDime** · 03-09-2007, 05:55 PM

Thanks..

Actually I was able to do it with much less hassle than I first thought.

I used the IF statement

=IF(E4="BASIC",$U$3,IF(E4="SS",$U$2)) and so on copied down, seems to work just fine!

Thanks again though..
Matt

**MattOneDime** · 03-13-2007, 12:33 PM

Well my little idea worked until the sheet grew to 8 descriptions.. haha (I guess excel is limited to 7 statements in one formula)..

Can anyone else give me some guidance on using vlookup? I have read the help files but I cannot see how to format it to have multiple results.

Thanks

**Paul** · 03-13-2007, 12:51 PM

Try using the VLOOKUP function. On your descriptions sheet, add a column before the descriptions so it's like this (the "." are there for spacing):

.....A.......................B
1 BASIC......This is a Basic Description
2 SS..........This is a SS Description
3 FL...........This is a FL Description
.
.
8 SRC.........This is a SRC Description

Then, on your main sheet, use this formula to pull out the 'BASIC', 'SS', etc. from after the hyphen and search for that text in the lookup array on the Description Sheet:

=VLOOKUP(RIGHT(A1,LEN(A1)-FIND("-",A1)),Sheet2!A1:B8,2,0)

This assumes that all part numbers are in the format "randomtext-YOURCODE". If there is data formatted with multiple hyphens, or such that it is "randomtext-randomtextYOURCODE" then this won't work properly.