Can't use match to compare two columns

**AC169** · 04-15-2009, 06:13 PM

Column A contains a list of 200 12 digit numbers, which includes a 6 digit part number.
Column B contains 20, 6 digit part part numbers.

How can I find where the numbers in column B crop up in column A?

Match is trying to find an exact copy where i'm only looking for an identical 6 digit string.

Thanks for any help- i've spent a few hour trawling various sites without success, just realising how little I know about excel.

**Morrigan** · 04-15-2009, 06:27 PM

Take a look at this post, sounds like something similar.

http://www.excelforum.com/excel-gene...in-a-cell.html

**daddylonglegs** · 04-15-2009, 07:49 PM

or you can convert the range to text within the formula so with no changes to data you can use this formula in C2 copied down

=MATCH("*"&B2&"*",INDEX(A$2:A$250&"",0),0)

This finds the 6 digit number anywhere with the 12 digit numbers. If it's always a specific 6 digits, say starting at position 3 then you should use a formula that would match only in that position, i.e.

=MATCH(B2&"",INDEX(MID(A$2:A$250,3,6),0),0)

**zbor** · 04-16-2009, 03:42 AM

Or, if you can put numbers in columns and searched words in row you can use find link in attach...

Book1.xls

**martindwilson** · 04-15-2009, 07:41 PM

you can use wild cards in match
=MATCH("*456789*",A2:A10,0) but your numbers need to be text numbers
. so select column A data/text to columns/next/next chose "text" fom column data format
click finish now column A will be text and you should find your partial matches

**AC169** · 04-16-2009, 05:15 AM

Thanks for the input it has helped

I’ve used the MID function to strip away the 12 digits to 6 & place the result in the next column.
I can now use MATCH to search the 6 digit numbers.

A new problem is MATCH is missing some values.
I’ve highlighted the columns & set all to text.
Copied the value & pasted into the find tool & this finds the cell ok.
Why would MATCH find some data & ignore other?

Andrew

**daddylonglegs** · 04-16-2009, 04:58 PM

If you use MID function to get the 6 digits then that result will be in text.

If the lookup value is numeric then you might not get a match. Once a cell has a value changing the format of that cell to text doesn't actually change the formatting. To check whether A2 is numeric or not

=ISNUMBER(A2)

To ensure that lookup value is text concatenate it with "", e.g.

=MATCH(A2&"",C2:C250,0)

where C2:C200 contains the MID formula