Link to home
Start Free TrialLog in
Avatar of Jim Metcalf
Jim MetcalfFlag for United States of America

asked on

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
Avatar of [ fanpages ]
[ fanpages ]

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.
Avatar of Jim Metcalf

ASKER

Here is a small sample of it that recreates what I am experiencing
1200.xlsx
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....

=IFERROR(MATCH(A1,B:B,0),"")

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.
ASKER CERTIFIED SOLUTION
Avatar of [ fanpages ]
[ fanpages ]

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thanks dude