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.
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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>
 
Upvote 0
Scott,

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

And thank you for your post
 
Upvote 0
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!!!!!!
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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...
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top