# If cell A1 contains (shirt,hat, or pants) then display (clothes) in B1 , If cell A1 contains (frog,snake,or lizard) then display (reptile) in B1 etc.

#### bvalkenb

##### New Member

Situation:

Column A has 1000's of qualitative survey responses. I would like to label / categorize the survey responses in a corresponding cell.

For example:

If Cell A1 contains: "shirt","pants","hat", then display "clothes" in B2

If Cell A1 contains: "sun", moon", "stars", then display "space" in B2

This formula needs to continue on because I have about 50 categories which will have keywords associated with them.

### Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

#### Scott Huish

##### MrExcel MVP
Make a table like I did in H1:I6 and use VLOOKUP:

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >stars</td><td >space</td><td > </td><td > </td><td > </td><td > </td><td > </td><td >shirt</td><td >clothes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >pants</td><td >clothes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >hat</td><td >clothes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >sun</td><td >space</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >moon</td><td >space</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td > </td><td >stars</td><td >space</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=VLOOKUP(A1,\$H\$1:\$I\$6,2,0)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

#### bvalkenb

##### New Member
Scott,

Please elaborate if possible. I'm not sure what to do based on your response.

And thank you for your post

#### bvalkenb

##### New Member
Doing exactly what you did works, so thank you! Let me play around with this some more in my document, and I'll re-post if I can't figure something out.

Thank you again for your quick response and insight!!!!!!

#### bvalkenb

##### New Member

One question:

What does the 2,0 refer to at the end of the formula?

#### Scott Huish

##### MrExcel MVP
The 2 means to return the result from the 2nd column of the table. The 0 (or FALSE) means it needs to be an exact match.

#### bvalkenb

##### New Member

Well, your formula worked when there is only one word in the qualitative response. The problem though is that they are all open-ended responses (sometimes multiple sentences), and the above formula is only returning #N/A, or #Value! when there is a positive match for one of the words. It does not work like it does in the simple table, where it labels the responses.

#### bvalkenb

##### New Member
Taking out the 0 (False) fixed the problem. Thank you again so much Scott!

#### bvalkenb

##### New Member
I was wrong, taking out the 0 did not fix the problem. It's not finding the appropriate words in the sentences now. Only if there is an exact match to the table...

#### Scott Huish

##### MrExcel MVP
That could have some problems because your list would need to be alphabetically sorted and it will match things that are close but not exact. So I have another idea then.

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" cellpadding="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:116px;" /><col style="width:147px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td >*</td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td><td >G</td><td >H</td><td >I</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td >I see the stars</td><td >space</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >shirt</td><td >clothes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >pants</td><td >clothes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >hat</td><td >clothes</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >sun</td><td >space</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >moon</td><td >space</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >6</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >*</td><td >stars</td><td >space</td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Spreadsheet Formulas</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >B1</td><td >=LOOKUP(9.999999999E+307,MATCH<span style=' color:008000; '>("*"&\$H\$1:\$H\$6&"*",A1,0)</span>,\$I\$1:\$I\$6)</td></tr></table></td></tr></table> <br /><br /><span style="font-family:Arial; font-size:9pt; font-weight:bold;background-color:#ffffff; color:#000000; ">Excel tables to the web >> </span>

Replies
10
Views
7K