Hi,
I need help matching the X (column A) and Y (column B) variables in the attached file with INDEX(MATCH) but I'm failing at the MATCH part.
The situation:
Both, X and Y can be numbers or numbers mixed with text. The Y variables always have 6 digits but the first digit might be a leading 0. The Y variables should represent the first 6 digits of the X variables, but the X variables should not have the leading 0.
The approach:
After removing the leading 0 from the Y variable (column C) I tried the following:
1) comparing Y with the left part of X, which is dependent on the length of Y
= Match (Y; Left (X; Length (Y);0)
2) I wasn't sure MATCH would work with LENGTH and LEFT, so I tried shortening X to the length of Y first (column G and H) and then match column B with G and H.
G = Left (X; 6)
H = Left (X; 5)
= Match (Y; G; 0)
= Match (Y; H; 0)
3) instead of of comapring X and Y with the same number of digits, I tried comparing Y* with X
= Match (Y & "*"; X;0)
None of my ideas work properly. I'm not sure why - might be due to formatting but I haven't been able to solve it that way. Any ideas?
Thank you.
Bookmarks