# Formula for yes or no for certain words.

#### hotrod

##### Board Regular
I have a 2010 excel spreadsheet. Column B is where I want the value to be yes or no when I type in a certain word in column A.
EG
Column A has – investigate, research, formula, test, results etc
Column B – I want the formula to recognise the words in column A to put in a yes or no against a the word.
investigate=yes, research=yes, formula=no, test=yes, result=no

### Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Like this?

Excel 2007
ABCDEFG
1WordYes/NoYes Words
2investigateYesinvestigate
3researchYesresearch
4formulaNotest
5testYes
6resultNo
Taxes2010
Cell Formulas
RangeFormula
B2=IF(ISERROR(VLOOKUP(A2,\$G\$2:\$G\$1000,1,FALSE)),"No","Yes")
B3=IF(ISERROR(VLOOKUP(A3,\$G\$2:\$G\$1000,1,FALSE)),"No","Yes")
B4=IF(ISERROR(VLOOKUP(A4,\$G\$2:\$G\$1000,1,FALSE)),"No","Yes")
B5=IF(ISERROR(VLOOKUP(A5,\$G\$2:\$G\$1000,1,FALSE)),"No","Yes")
B6=IF(ISERROR(VLOOKUP(A6,\$G\$2:\$G\$1000,1,FALSE)),"No","Yes")

Another way:

=LOOKUP(A1,{"Formula","Investigate","Research","Results","Test"},{"yes","yes","no","yes","no"})

c_m_s_jr - works perfectly. Thanks heaps

BiocideJ - your formula did not work. When there is no text entered into column a, column b has no.

Replies
6
Views
313
Replies
5
Views
124
Replies
3
Views
204
Replies
12
Views
232
Replies
9
Views
254

1,203,555
Messages
6,056,070
Members
444,841
Latest member
SF_Marnie

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

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