# 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

###### Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x

Commented:
For whole Column A:
=COUNTIF(\$A:\$A, "*" & \$A2 & "*")>1
0

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

Author 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

Commented:
Try formula below for cell B2
=COUNTIF(A\$2:\$A\$6, "*" & A2 & "*")>1
0

Microsoft MVP ExcelCommented:
>>A12345-a      FALSE
Bxxxxx-a              FALSE
A12345            TRUE
Bxxxxx            TRUE
A12345-2      FALSE

why would Bxxxxx be True???
0

Finance AnalystCommented:
Would sorting the data on that field to put similar entries together be an option?

Thanks
Rob H
0