Match function not working correctly in excel

I have two long columns I am trying to run a match on.  'A' column 800 rows the other 'b' 400

=match(A1,B:B)   is the code and then I copy it down...   the code populates correctly with the a1 changing in sequence as it should.

the first 100 or so rows perform correctly and then all of a sudden there are a ton of hits the rest of the way through a but most of them
are matching the same cell in column b that actually doesn't match.

has anyone seen  this before?

I run a match on the
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

[ fanpages ]IT Services ConsultantCommented:
Are the values in either column [ A ], or column [ B ], results of in-cell formulae?

If so, is the Calculation Mode set to 'Manual', & a Calculation of the column(s)/worksheet is required?

Perhaps posting an attached workbook where you see this issue would help us resolve your issue.
jamesmetcalf74Author Commented:
Here is a small sample of it that recreates what I am experiencing
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
You are omitting the Match_Type argument in the Match formula which makes it to lookup for approximate value. I am not sure if you are aware of this.

Try the following formula and see if this works for you....


Open in new window

The above formula will return the row number if it finds an exact match of A1 in col. B else it will return a blank.
[ fanpages ]IT Services ConsultantCommented:
If you "correct" the formula in column [C], using the third parameter "match_type" set to 0 (Exact match)...

So, cell [C1] is

Copying down column [C] shows that all the MATCH() functions fail (with #N/A).

None of the values in column [A] exist in column [ B ].

To test this, set the formula within cell [C1] as above, & copy down the column to [C13].

Next, copy the range [A1:A13] to [B14:B26].

Cell [C1] will then show 14, [C2] is 15, [C3] is 16, ... [C13] = 26.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jamesmetcalf74Author Commented:
Thanks dude
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.