Concatenate and Match Three Fields

[richmcgill]

I need to concatenate row A, B AND C to match to row D and provide results in column E. If concatenate is not the nest way I am open to ideas. I am matching tens of thousands of these items on a monthly basis.

Does this make any sense?

One issue I have is the number in column D has a - after the first three numbers which is what is in column A.

One of the other issues I have is:
Row 1 needs to be three digits but my report will not show the leading zero. An 11 would show as an 11 but I think for my match to work it would need to be an 011?
Row 2 needs to be six digits but my report will not show the leading zero. An 012345 would show as an 12345 but I think for my match to work it would need to be an 012345?
Row 3 has one digit and if the number is a zero it will show as a zero

 

[Joe4]

That is because formulas work off of actual values, not the formats you apply to numbers.
So if you look at the formula box while on cell A2, I bet you will see that the actual value in that cell is 11, and is just formatted to show three digits.

But you can apply that same logic in your concatenate formula by using the TEXT function.
So where you have A2 currently in your concatenation formula, replace it with:

TEXT(A2,"000")

and repeat the logic for B2, i.e.

TEXT(B2,"000000")