Formula if Cell A contains any number in Cell B.

wedding

Board Regular
Joined
Jun 12, 2010
Messages
127
Hi I am currently learning and Iwill make this simple:

If Cell A = 2

If Cell B = 9,10,11,12

If Cell A dont contain any numbers in B then Cell C will = +1
If Cell A dont contain any numbers in B then Cell C will = -1

My I know the formula to Cell C? Thanks Guys for the great help in the forums!

Just to add, the values in Cell A and Cell B are from a formula so there are always different.
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
It's not clear if you're incrementing (up/down) the value in C1 or C1 is to be either 1 or -1 ?

Assuming the latter:

=IF(ISNUMBER(SEARCH(","&A1&",",","&B1&",")),1,-1)

(above assumes a comma delimiter between values)
 
Upvote 0
ok i will post an example:
21lk93r.jpg


In this case, if Cell A contains any value from Cell B then Cell C will be an 8, if not -4)

Hope this is clearer=]
 
Upvote 0
Do you think that you can use FIND and if the value is >0, then +1

The only thing you need to be careful of using either SEARCH / FIND is false positives hence the "normalising" of the string... ie 2 would otherwise be found in 21,22
,2, would not be found in ,21,22,
 
Upvote 0
Try this?

=IF(ISERROR(SEARCH(A1,B1)),-4,8)

Where A1 has the number you are looking for in the string of numbers in B1.

Does that help?
 
Upvote 0
The only thing you need to be careful of using either SEARCH / FIND is false positives hence the "normalising" of the string... ie 2 would otherwise be found in 21,22
,2, would not be found in ,21,22,


I wouldn't have thought of that. Thank you.
 
Upvote 0
The only thing you need to be careful of using either SEARCH / FIND is false positives hence the "normalising" of the string... ie 2 would otherwise be found in 21,22
,2, would not be found in ,21,22,

Good point on that!! :eek: Ok, maybe try this one?

=IF(AND(ISERROR(SEARCH(A1&",",B1,1)),ISERROR(SEARCH(","&A1&",",B1))),-4,8)
 
Upvote 0

Forum statistics

Threads
1,213,531
Messages
6,114,167
Members
448,554
Latest member
Gleisner2

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