After a little more research I have discovered that the part numbers that
contain mixed alpha and numeric values match exactly, the only ones that fail
are the those that are all numeric.

I was apparently supposed to have had this out yesterday - oops, so I have
written a sub that has done the trick

In response to your question Ken, the comparison produces a TRUE result.

--
Regards - Peter



"Ken Wright" wrote:

If you try a simple comparison of the two cells that you believe are
identical and should return a positive result, what do you get? eg Assuming
you had two values in say A1 and F3, then a simple =A1=F3 would either
return TRUE or FALSE. What does thsi give on your two values?

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

------------------------------Â*------------------------------Â*----------------
It's easier to beg forgiveness than ask permission :-)
------------------------------Â*------------------------------Â*----------------





"Peter" wrote in message
...
The error that is returned is "#N/A"


--
Regards - Peter



"Pete_UK" wrote:

You will need to have the match-type parameter set to 0 to look for an
exact match - what error does this return?

Pete

On Oct 30, 9:45 pm, Peter wrote:
I have a spreadsheet with 2 lists of data, each list contains a number
of
columns, the 2 lists are not equal in length, list 1 is about 2,600
rows and
list 2 is about 50,000 rows

The left most column in each list contains part numbers, these are
unique
values that sometimes contain alpha characters and sometimes not.

The first list is a 'finished' list that I wish to use for display, but
the
second list has a column that contains a statistic that I have been
asked to
incorporate into the finished list.

The second list also has most of the part numbers present in the first
list
(about 94%), so I didn't think the task would be too hard, a relatively
simple formula should do the trick - but my formula has returned
results that
I do not understand.

My formula is thus:

"=IF(COUNTIF($Q$2:$Q49959,A2636)<0,OFFSET($Q$1,MA TCH(A2636,$Q$2:$Q$49959,1-),4,1,1),0)"

Now the formula works fine with the exception of the MATCH function,
the
cell A2636 has the part number 1672312 with no spaces before or after.

The part number 1672312 exists in the second list in cell Q1557, again
with
no spaces before or after and as can be seen by the formula, the value
I want
is in the fifth column of the list.

The MATCH function seems to want to match to a part number 167-2222
which is
located in the cell Q7638.

I tried altering the format of the part number columns to text, but it
made
no difference. I also tried altering the MATCH_TYPE argument of the
function
from 1 to 0 to force an exact match, the formula then returns an error.

Now I do not consider myself an Excel novice and I know that this task
would
be easily accomplished with a little VBA, but that's not the point. I
have
read the help file again to make sure that I was using the MATCH
function
correctly. I tried alternatives like VLOOKUP to see what it matched to,
again
it matches to the same incorrect cell.

I suspect formatting of the part number columns somehow, but I don't
know
how - the part number columns contain absolute values, no underlying
formula's, no linked cells or anything like that. Using the Formula
Evaluate
tool shows me that the cell A2636 evaluates to the correct part number,
but
from there is goes south.

Anyone have any ideas?
--
Regards - Peter