MATCH function

[Chris Barlow]

Hi All

I am using the following formula in each row of column B

=MATCH(A1, $C$1:$C$685,0)

Col A Col B Col C
84048 #N/A 86316
86948 #N/A 86317
86960 #N/A 86319
86964 #N/A 86960
86970 #N/A 84048
87288 #N/A 86620
87291 #N/A 86622

You'll notice that item 1 and 3 in column A have matches in column C
but the formula is not picking them out. I have used MATCH many times
before without problem but this has got me stumped.

Can anyone shed any light on problem?

Many thanks
Chris Barlow

 

[Biff]

Hi Chris!

Could be unseen characters like spaces in the columns! Use
a LEN( ) test to see if the length matches what you
actually see and compare LEN( ) col A, cell that holds
84048 to LEN( ) col B, cell that holds 84048.

Biff

 

[Frank Kabel]

Hi
check that both values are really numbers. What do the following
formulas return
=ISNUMBER(A1)
=ISNUMBER(C5)
=A1=C5

All should return TRUE

 

[Chris Barlow]

Thanks Frank and Biff.

The Len showed both columns to be 5 characters.

Although when I look at cell format for column A and C it shows cell
to be numbers the IsNumber was giving False results. I overtyped on
of the numbers and it worked!

So, not quite sure why the problem exists but at least I know what i
is now.

Cheers Guys.

Chris Barlow: