=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

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.

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


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=509337

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.

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


--
gjcase
------------------------------------------------------------------------
gjcase's Profile: http://www.excelforum.com/member.php...o&userid=26061
View this thread: http://www.excelforum.com/showthread...hreadid=509337

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
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=509337

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


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=509337

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


--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=509337

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

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

Thankyou

Chris


+-------------------------------------------------------------------+
|Filename: Product.doc |
|Download: http://www.excelforum.com/attachment.php?postid=4332 |
+-------------------------------------------------------------------+

--
cj21
------------------------------------------------------------------------
cj21's Profile: http://www.excelforum.com/member.php...o&userid=25673
View this thread: http://www.excelforum.com/showthread...hreadid=509337

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

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

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