Excel FIND and MATCH across entire column

Hi,

I would like to be able to take the text string of a cell, run a find function across an entire column, and return whether or not the text string exists within other cells.

E.g. The first value below would be found in the third value and the fifth value.  Whilst it would be great to return the location(s) of the third value and the fifth value, it would be fine if it would simply return a TRUE or FALSE.

A12345
B55433
A12345-a
C6453
A12345-2
E324532

Thanks for your assistance.
SingamajigAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
chwong67Connect With a Mentor Commented:
For whole Column A:
=COUNTIF($A:$A, "*" & $A2 & "*")>1
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
Hello,

for a simple TRUE or FALSE, try

=IFERROR(LOOKUP(1,FIND(A1,A2:A6),ROW(A2:A6))>0, FALSE)

If you use just

=LOOKUP(1,FIND(A1,A2:A6),ROW(A2:A6))

The result will be the row of the LAST value found. Iferror is not available in Excel 2003 or earlier. For these versions, you could use

=IF(ISERROR(LOOKUP(1,FIND(A1,A2:A6),ROW(A2:A6))>0), FALSE,LOOKUP(1,FIND(A1,A2:A6),ROW(A2:A6))>0)

cheers, teylyn
0
 
SingamajigAuthor Commented:
Thanks for this.

Is it possible to lookup the entire column so that it would consider all items in the column, and not just the items below?

I've re-done the example below, with the expected results
A12345-a      FALSE
Bxxxxx-a              FALSE
A12345            TRUE
Bxxxxx            TRUE
A12345-2      FALSE

Cheers
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
chwong67Commented:
Try formula below for cell B2
=COUNTIF(A$2:$A$6, "*" & A2 & "*")>1
0
 
Ingeborg Hawighorst (Microsoft MVP / EE MVE)Microsoft MVP ExcelCommented:
>>A12345-a      FALSE
Bxxxxx-a              FALSE
A12345            TRUE
Bxxxxx            TRUE
A12345-2      FALSE

why would Bxxxxx be True???
0
 
Rob HensonFinance AnalystCommented:
Would sorting the data on that field to put similar entries together be an option?

Thanks
Rob H
0
All Courses

From novice to tech pro — start learning today.