Solved

# Excel FIND and MATCH across entire column

Posted on 2011-10-20
260 Views
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

0
Question by:Singamajig

LVL 50

Expert Comment

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 Comment

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

LVL 9

Expert Comment

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

LVL 9

Accepted Solution

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

LVL 50

Expert Comment

>>A12345-a      FALSE
Bxxxxx-a              FALSE
A12345            TRUE
Bxxxxx            TRUE
A12345-2      FALSE

why would Bxxxxx be True???
0

LVL 31

Expert Comment

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

Thanks
Rob H
0

## Featured Post

I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …