Indicator (Yes or No) when the row has a string of texts and numbers

[managingcrap]

I'm trying to find a function that allows me determine if the number is present in a given rows of texts and number.

I'm having a problem with this one because the row has semi-colon. Please see attached.

The function should be able to determine if the number is present in the rows or not. If it is, the function will result to YES. If not, then a No.

Please see attached file. Thank you.

[Speshul]

Try this in the yellow cells in your sample sheet

=IF(ISERROR(SEARCH(B7,B2)),"No","Yes")

Breakdown:


ISERROR(SEARCH(B7,B2)) This is basically stating that the formula SEARCH(B7,B2) will result in an error. The search function will look for the string in B7, within the string in B2. So it is looking for "1.4" in the cell containing "1.1; 1.2; 1.3; 1.5". If it is NOT found, it will return an error.
So, if it is not found, ISERROR is true, since the formula is an error. If it is found, you get a False. This True or False result feeds into the Logical Test of the IF function that contains it.

The IF function works like below;


=IF(logical_test,value_if_true,value_if_false)

logical_test: This is where you put the test. The rest can be any statement that results in a True or False result, for example A1>15. If A1 is in fact larger than 15, the result will be True. Common operators would be
.

• ">" Greater Than
• "<" Less Than
• "<>" NOT equal to
• ">=" Greater than or equal to
• "<=" Less than or equal to
• "=" Equal to

value_if_true: This is the result if logical_test returns a TRUE value.
value_if_false: As you probably guessed, this is the result if logical test returns a FALSE value.


A full example of the formula would be:

=IF(A1=10,A1+10,B1)

Now, if cell A1 is equal to 10, the result of the formula will be 20. Why? Because if A1=10, the value_if_true will occur, resulting in 10+10
If A1 is NOT equal to 10, the result of the formula will be whatever the value of B1's cell is

Remember, any one of the three inputs for the IF function can be another formula, for example, here are two IF's nested:
=IF(A1=10,A1+10,IF(A1>10,A1,B1-10))
The value_if_false is another IF function, that will only be executed by Excel if the previous IF statement (A1=10) is False

[managingcrap]

I forgot to add a note that the reference is not only for each row but for all the rows, meaning that if I had 1.3 on cell B7, the function will look into rows 2 to 4 instead of just row 2. Sorry about that.

[Speshul]

How many rows are we talking?

[Speshul]

Ok. Here's what you gotta do.


Use this formula:

=IF(ISERROR(SEARCH(B7,CONCATENATE(TRANSPOSE(B2:B4)))),"No","Yes")


Select the highlighted portion, exactly!
=IF(ISERROR(SEARCH(B7,CONCATENATE( TRANSPOSE(B2:B4) ))),"No","Yes")

Press the F9 Key

It will turn into this:

=IF(ISERROR(SEARCH(B7,CONCATENATE({"1.1; 1.2; 1.3; 1.5","1.3; 1.5; 1.1; 1.6","1.1; 1.6; 1.3; 1.2 1.4"}))),"No","Yes")

Remove the RED brackets and press Enter { }


Drag the formula down.

[samba_ravi]

=IF(COUNTIF($B$2:$B$4,"*"&$B7&"*")>0,"Yes","No")
try this formula
and copy towards down

[managingcrap]

=IF(COUNTIF($B$2:$B$4,"*"&$B7&"*")>0,"Yes","No")
try this formula
and copy towards down

It works. Question: What does the "*" do? Thanks

[managingcrap]

Ok. Here's what you gotta do.


Use this formula:

=IF(ISERROR(SEARCH(B7,CONCATENATE(TRANSPOSE(B2:B4)))),"No","Yes")


Select the highlighted portion, exactly!
=IF(ISERROR(SEARCH(B7,CONCATENATE( TRANSPOSE(B2:B4) ))),"No","Yes")

Press the F9 Key

It will turn into this:

=IF(ISERROR(SEARCH(B7,CONCATENATE({"1.1; 1.2; 1.3; 1.5","1.3; 1.5; 1.1; 1.6","1.1; 1.6; 1.3; 1.2 1.4"}))),"No","Yes")

Remove the RED brackets and press Enter { }


Drag the formula down.

Works, too. you guys are genius. Thanks