Match Function only matching if cells are clones Answered

[stuarts.burgers]

I am attempting to run the Match Function using data from different sources, and the Match will not work unless the “full_road_name Data” is copied and pasted from one sheet to the other.
I have 3 sheets in a workbook and do a match across Sheets 1 and 2, and Sheets 2 and 3
If I Copy and Paste Data from Sheet 1 or Sheet 3 into Sheet 2 in the “full_road_name” column, the match works.
If I type it in or Copy and Paste Data from another spreadsheet, the Match does not work.
I have attached a sample of my work

On the Mailman Route Sheet,
cell A2 was copied and pasted from the Delivery Adress Sheet
cell A3 was typed
Cell A4 was copied and pasted from a completely different workbook

With Cells A2 and A3 I do no further formatting but A4 needed to change font point size and was centered in the cell.

If you look at the leading edge of the 1st letters in the 3 different cells, they do not line.

I am using the following match formula on the
Delivery Adress sheet

=MATCH(B2&A2,$‘Mailman’‘s Route’.A$1:A$552&$‘Mailman’‘s Route’.B$1:B$552,0)

and
On nz-adresses I am using
=MATCH(B2&A2,$‘Mailman’‘s Route’.A$1:A$552&$‘Mailman’‘s Route’.B$1:B$552,0)

Can someone help me get the formatting right or give me a different formula that will take these differences in letters into account?

test file.xlsx (7.1 KB)

Thank you for your help with this

Stuart

[EarnestAl]

Allowing for the trailing spaces with TRIM =MATCH(B2&A2;TRIM($'Mailman''s Route'.A$1:A$552)&TRIM($'Mailman''s Route'.B$1:B$552);0)

[stuarts.burgers]

Thank you, it works nicely. I am sure I could have got there in the end with Chat GPT, etc, but having the human interaction.
If I am clumsy in asking a nice person like yourself, will help me.

Thanks again

Stuart

[fpy]

@stuarts.burgers

different formula

=MATCH(B2&"?"&A2,$'Mailman''s Route'.A$1:A$552&$'Mailman''s Route'.B$1:B$552,0)

for the gory detail(s), see List of Regular Expressions :

but you probably should consider a consistent use of TRIM Function

[stuarts.burgers]

Hi
Thank you for your assistance.
I find TRIM works very well
It’s nice we still have the human interaction, as often I understand what I am attempting to do, but to articulate it to Chat GPT etc, can be my downfall

Thanks again Stuart