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.
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123
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)
 

wedding

Board Regular
Joined
Jun 12, 2010
Messages
127
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=]
 

cb711

Board Regular
Joined
Dec 18, 2009
Messages
107
Do you think that you can use FIND and if the value is >0, then +1
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

Change 1 and -1 in prior suggestion to 8 and -4 respectively.
 

wedding

Board Regular
Joined
Jun 12, 2010
Messages
127
Yea DonkeyOte you got what I meant the first time=] Thanks a lot!
 

DonkeyOte

MrExcel MVP
Joined
Sep 6, 2002
Messages
9,123

ADVERTISEMENT

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,
 

AlisaA

Board Regular
Joined
Mar 17, 2010
Messages
193
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?
 

cb711

Board Regular
Joined
Dec 18, 2009
Messages
107
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.
 

AlisaA

Board Regular
Joined
Mar 17, 2010
Messages
193
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,122,658
Messages
5,597,403
Members
414,142
Latest member
Banyangt

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
Top