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
Joined
Oct 2, 2014
Messages
13
Please help. I have been researching and have been unable to find the correct solution.

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.

Thank you in advance for your suggestions.
 

Some videos you may like

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
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Oct 2, 2014
Messages
13
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
Joined
Oct 2, 2014
Messages
13
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
Joined
Oct 2, 2014
Messages
13

ADVERTISEMENT

One question:

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

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Joined
Oct 2, 2014
Messages
13

ADVERTISEMENT

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
Joined
Oct 2, 2014
Messages
13
Taking out the 0 (False) fixed the problem. Thank you again so much Scott!
 

bvalkenb

New Member
Joined
Oct 2, 2014
Messages
13
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
Joined
Mar 17, 2004
Messages
19,955
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

Try this instead:

<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>
 

Watch MrExcel Video

Forum statistics

Threads
1,109,521
Messages
5,529,325
Members
409,863
Latest member
stacy09
Top