Problem with =Match in Libre Calc

asked 2019-04-20 02:46:45 +0200

stuarts.burgers gravatar image

I am using the formula "=MATCH(E2&D2,'Mailman''s Route'.A$2:A$500&'Mailman''s Route'.B$2:B$500,0)” Where Mailmans route is a list of Street Names in Colum A and Street Numbers in Colum B If I cut and paste the Street Name into Colum A from the data set that I wish to find matches in then everything works perfectly. If I type the street names into Colum A then some streets will do a match and others not

Mailmans Route

In the above example, both Glastonbury Drive and Quantock Place are in the data set only Glastonbury gets matched with Quantock not being matched. I have cut and paste Quantock for Quantock 2 below and only then it will match (see below)

Cut and Paste Mailmans Route

Quantock 2 will match but Quantock 4 etc will not

This has me stumped can a nice person help

Thanks Stuart

edit retag flag offensive close merge delete

Comments

Sounds like some of the cells containing invisible characters (indicated by the fact that, if you copy [also copies invisible characters] the cell content into the search it works, while if just entering by keyboard, some work and some do not). Even a blank at the end of a cell would cause a MATCH failure since you are using type "0".

But this is almost impossible to diagnose from screenshots of the data; real data in an anonymized test case would be required.

Opaque gravatar imageOpaque ( 2019-04-20 11:16:45 +0200 )edit

Thanks I had a further look and found that f there was three parts to a street name ie "Old Mill Road" there is two spaces between elements two and three where between elements one and two there is only one space. So back to the drawing board Thanks for your help

stuarts.burgers gravatar imagestuarts.burgers ( 2019-04-20 22:30:18 +0200 )edit