Matching

[cj21]

=MATCH(A2,$E$2:$E$1122,0)

I am using this formula for matching, however for some values it does not work, even though there is a match. Can anyone help?

Chris

[Mark Lincoln]

With a Match_Type of 0, you're looking for exact matches. Might there
be unseen differences in the data that appear to match?

This could happen due to rounding; for example, a lookup value of 2.5
would appear to match a cell that reads "2.5" in your lookup range.
But if that "matching" cell is formatted to show a single digit and the
value is actually 2.48, there is not actually an exact match.

[cj21]

No the numbers are not rounded. Could it be because of formating?

[Mark Lincoln]

Are A2 and each cell in your lookup range all formatted as numbers? If
a cell in the lookup range is formatted as text (or if the number is
entered with a leading apostrophe, which forces the cell to be text),
and A2 is a number, that will likely result in a mismatch.

[gjcase]

Likewise trailing spaces or unprintable characters. You might check out the CLEAN function.

[cj21]

I just cant get this match thing to work!

On another spreadsheet which is the same format it does work.

What can i do?

Chris

[cj21]

I have tred the CLEAN function and the format is fine. The march formula works perfectly on another spreadsheet.

[cj21]

I would like to post my file as an attachment but it's too big.

[Jerry W. Lewis]

When I try to download your file, I get an "Invalid Attachment specified"
message from excelforum. Besides it is usually more informative if you can
answer the following few questions about your situation.

What is in A2 and what is in a cell that should match.

What is the result of ISNUMBER() for A2 and the supposedly matching cell?

If the cells contain formulas, trace back the contents of referenced cells
until you get to actual inputs. Alternately if the cells contain numbers,
give the results of D2B() or D2D() for A2 and the supposedly matching cell,
where the VBA code for these functions is given at
http://groups.google.com/group/micro...06871cf92f8465

Jerry

"cj21" wrote:

>
> =MATCH(A2,$E$2:$E$1122,0)
>
> I am using this formula for matching, however for some values it does
> not work, even though there is a match. Can anyone help?
>
> Chris
>
>
> --
> cj21
> ------------------------------------------------------------------------
> cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
> View this thread: http://www.excelforum.com/showthread...hreadid=509337
>
>

[cj21]

I can explain it best with the example. Try this new attachment.

Thankyou

Chris

[Jerry W. Lewis]

It still will not download (same error message), but since your question is
why MATCH() does not declare a match, all that is needed is the answers to my
previous questions (which is all I would look at if I were able to download
your file).

Jerry

"cj21" wrote:

>
> I can explain it best with the example. Try this new attachment.
>
> Thankyou
>
> Chris

[cj21]

The example is wrong, so it's best ignored. In column A i have a list of products which are 8-digits long, the first 500 of which start with a 0. In column E i have a list of product codes that i want matched.

The match formula matches the products beginning with 0, but ignores the rest.

Chris

[Jerry W. Lewis]

In my experience, MATCH works as it should. Therefore exploring claims that
it is not working generally begin with understanding what is actually
present, specifically:

What is in A2 and what is in a cell that should match it.

What is the result of ISNUMBER() for A2 and the supposedly matching cell?

If the cells contain formulas, trace back the contents of referenced cells
until you get to actual inputs. Alternately if the cells contain numbers,
give the results of D2B() or D2D() for A2 and the supposedly matching cell,
where the VBA code for these functions is given at
http://groups.google.com/group/micro...06871cf92f8465

Jerry

"cj21" wrote:

>
> The example is wrong, so it's best ignored. In column A i have a list of
> products which are 8-digits long, the first 500 of which start with a 0.
> In column E i have a list of product codes that i want matched.
>
> The match formula matches the products beginning with 0, but ignores
> the rest.
>
> Chris
>
>
> --
> cj21
> ------------------------------------------------------------------------
> cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
> View this thread: http://www.excelforum.com/showthread...hreadid=509337
>
>