Matching

C

cj21

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

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

Chri
 
M

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.
 
M

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.
 
G

gjcase

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

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
 
C

cj21

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

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/microsoft.public.excel/msg/b106871cf92f8465

Jerry
 
J

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
 
C

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
 
J

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/microsoft.public.excel/msg/b106871cf92f8465

Jerry
 
Top