# Test a value in a cell

#### macca99

##### New Member
Hi.

I would like to test a cell value in a range of cells in a specific row and if not found enter text in an adjacent cell.

eg if a cell in the range B2:H2 does bot contain a letter S then in cell A2 show "Does not contain S" Then continue to the next row and do the same test.

TIA

### Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},\$Z\$1:\$Z\$99,\$Y\$1:\$Y\$99),2,False) to lookup Y values to left of Z values.

#### Richard Schollar

##### MrExcel MVP
Is that formula or VBA? Formula wise could be in A1:

=IF(COUNTIF(B2:H2,"*S*"),"Value if contains S","Does Not Contain S")

altho this won't be case sensitive (wasnt sure if this was important)

#### macca99

##### New Member
Thanks Richard.

Case is not important but I trialled your formula and it gave all "Does Not contain S" even though some did have an S.

Macca

#### macca99

##### New Member
Richard you put me on the right track and this is formula worked.

=IF(COUNTIFS(B2:H2,"S")>0,"Has an S", "Does not have an S")

Thanks

#### Scott Huish

##### MrExcel MVP
Richard's formula should work, but if you mean if it contained an S, it would only contain an S and nothing else, which is indicated by the formula you gave, then you should only need:

=IF(COUNTIF(B2:H2,"S"),"Has an S","Does not have an S")

#### macca99

##### New Member
Just tried it again and indeed it did work this time (not sure what i did)

Thanks

Replies
1
Views
262
Replies
1
Views
259
Replies
3
Views
422
Replies
4
Views
460
Replies
3
Views
1K

1,195,635
Messages
6,010,834
Members
441,569
Latest member
PeggyLee

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

### Which adblocker are you using?

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

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