"Sking" wrote:
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.