Match issue

[Sking]

Hi,

I'm using this as part of a larger formula, but I think I've narrowe
the issue down to MATCH.
I've taken out a small section of the sheets I'm working on t
experiment on, it consists of one sheet with 5 numbers in a column, an
a second sheet with 5 numbers and =MATCH(A1,Sheet2!A:A,0).
One of these numbers matches and should give me a value (rather than th
#N/A I'm getting).

If I type in the matching value manually it does work and match gives m
a value of 1 (as it should). - this isn't practical for the full datase
though.

I've changed the format of both sets of numbers (to both general an
number) so I don't think it's a simple format issue.

I've checked for a ' before the number or spaces after.

Does anyone have any ideas about what else I could try?

Let me know if I've missed anything out or more information is needed.

Thank

 

[joeu2004]

I've taken out a small section of the sheets I'm working on to
experiment on, it consists of one sheet with 5 numbers in a column,
and a second sheet with 5 numbers and =MATCH(A1,Sheet2!A:A,0).
One of these numbers matches and should give me a value (rather
than the #N/A I'm getting).

In short, change your formula to:

=MATCH(ROUND(A1,2),Sheet2!A1:A10000,0)

Change ROUND(...,2) to whatever precision is appropriate for your numbers.

Alternatively, you might want round the result in A1. Whether or not that
is a good idea depends on the design of your worksheet and application.

Note that I changed Sheet2!A:A to Sheet2!A1:A10000. Change A10000 to
whatever is appropriate for your expected data.

Explanations....

The problem is that numbers are not what they always appear to be, even when
they are formatted to 15 significant digits, the most that Excel will
format.

For example put =10.1-10 into A1, and put 0.1 into A2. The formula
=MATCH(A1,A2,0) returns #N/A, indicating no match. But
=MATCH(ROUND(A1,2),A2,0) returns 1 as expected.

The reason is that the calculated 0.1 in A1 is infinitesimally less than
0.1. For that reason, even =MATCH(A1,A2,1) returns #N/A. (Sometimes,
MATCH(...,1) will __seem__ to remedy the problem. But it is only by
coincidence, as demonstrated.)

The explanation is complicated. I can explain, if you are interested. Let
me know. Alternatively, look at http://support.microsoft.com/kb/78113.
Caveat: KB 78113 is __essentially__ right, but it is technically wrong in
some details. For example, Excel does __not__ "stor[e] only 15 significant
digits of precision".

As for Sheet2!A:A....

That might work well enough in Excel 2003 and earlier, since it is limited
to 65536 rows.

But in Excel 2007 and later, A:A refers to 1,048,576 rows.
MATCH(...,Sheet2!A:A,0) must search all 1 million rows before failing. That
can slow down recalculations significantly.

Most applications never need 1 million rows. Even 10,000 or 100,000 rows is
probably more than you will ever need. But time to search 100,000 rows is a
lot better than 1 million rows.

 

[joeu2004]

The problem is that numbers are not what they always appear
to be, even when they are formatted to 15 significant digits,
the most that Excel will format.

For example put =10.1-10 into A1, and put 0.1 into A2. The
formula =MATCH(A1,A2,0) returns #N/A, indicating no match.
But =MATCH(ROUND(A1,2),A2,0) returns 1 as expected.

I forgot that we __can__ see why A1's 0.1 is not the same as A2's 0.1 if we
format them to __16__ decimal places. In fact, A1 is about
0.0999999999999996, which is 15 significant digits.

Here's a better example....

Put =(1001/1E6)*1E6 into A1, and put 1001 into A2. Note: 1E6 is one way to
write 1000000 (1 million).

Then, MATCH(A1,A2,0) and MATCH(A1,A2,1) return #N/A, indicating no match.
But MATCH(ROUND(A1,2),A2,0) returns 1 as expected.

That is because the result of the computation in A1 is infinitesimally less
than 1001. It is exactly 1000.99999999999,98863131622783839702606201171875.

I use the comma after the decimal place to demarcate 15 significant digits,
the most that Excel formats. When formatted to 15 significant digits,
Excels rounds the 16th digit and beyond (9886...).

That is why Excel displays the value as if it is 1001, even when formatted
to 15 significant digits (11 decimal places, in this case).

PS: This example also demonstrates that computer arithmetic differs from
mathematical arithmetic. Of course, (1001/1E6)*1E6 is 1001 mathematically.
That is because mathematical arithmetic has unbounded precision. But
computer arithmetic has limited precision. That limitation often causes
infinitesimal differences.

 

[Sking]

Thank you very much!

That was both useful and fascinating, I never knew that about excel.

My formula's working perfectly no