Search an alphanumeric in a column and return true/false

[2226]

I am having some revision numbers say c1, c2,c3,c4.... I want to know how to
make a search in column that contains C1 to C100 randomly (may be some
missing) and return true if that is present and false if it is not.

 

[Max]

One equivalent way

Assume the base list is in col A
Assume your own list is in B2 down

Put in C2: =IF(B2="","",IF(COUNTIF(A:A,B2)>0,"Present","Not found"))
Copy C2 down to the last row of data in col B

Col C will return:
Present for items in col B found in col A
Not found for items in col B not found in col A

You could then easily apply autofilter on col C to filter out as desired
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[2226]

Thank u Max,
I have tried ur logic but it is not working, all results are "not found".
Let me explain once again my prob, in one cell I have an input say "C3" (say
cell no. A10), I need to check whethere that is present in a column(say cell
no. B10 to B20), if it is present then it should return true or else it
should return false. For this I need to write a function in the cell say
"cell no. S10"

 

[Ashish Mathur]

Hi,

In cell S10, use the following formula and then copy down:

=if(countif(B10:B20,C3)>0,"True","False")

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[Max]

Believe there was nothing wrong with the logic in the response earlier
Anyway, since you have revised your description
Put in S10:
=IF(A10="","",IF(COUNTIF(B10:B20,A10),TRUE,FALSE))

If the above settles it for you (it should),
pl press the YES button below to acknowledge
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik

 

[2226]

Thank u for ur time, still I’m not able to find correct logic. See I have a
column in which there are alphanumerical in cell B10 to B20 (red 3, red4,
red6, red9, red10, red 15, red17, red18, red20, red23). I have cell in which
the input has given say cell A10 (red5) & I have a cell which will tell
whether input in A10 is present from the B10 to B20 or not say cell S10. If
I’m putting the logic statement in cell S10 the logic is results in “falseâ€,
when I change the input in A10 as red4, which is present in cell B10 to b20
then also the answer is “false†but it has to be “true"

 

[Pete_UK]

You need to be more consistent with your data. In your examples you
quote red 3 and red 15 (i.e. with spaces in them), but also red4, red5
etc (without spaces). I suspect this is what is causing you problems.

Hope this helps.

Pete

 

[xlm]

I have test Max's formula and its works.
The database need to consistency. You can't have a range of cells with
"red 3", "red10"..etc where there are space in some cells and not in others.

Try changing all your data table to "red 3", "red 10" by adding a space in
between the word and number, then place the formula in the cells you want.
Bear in mind that the value in A10 should also be the same as your data in
col B.

HTH
--
If this posting was helpful, please click on the Yes button below

Thank You

cheers, francis

 

[2226]

I apologize Max, ur logic function works perfectly. I have used it on a wrong
sheet where I had made so many changes to get the results, it actually works.
I had a problem with the work sheet. When I tried it on the other sheet it
was working. Thank u very much making my life easy. Thank u Max.

 

[Max]

Ah, no prob. Just glad you got it going over there.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:22,000 Files:370 Subscribers:66
xdemechanik