Seeking to use MATCH, ignoring first character

[auto.pilot]

I have a long column of populated data that contains both numbers and letters (source). On a separate sheet, I have another column with the same information in a different order (destination). In the source column, the first character is missing from several entries. I need a method using MATCH on the destination sheet that will match all but the first character. Other considerations: I can't sort either worksheet. All entries in the destination column are 9 characters. Some of the entries in the source column are 8 characters (first one missing).

I've searched but can't find anything quite like this.

Would appreciate any thoughts.

Thanks

Jim

 

[JayBurn]

You can use an array to do it.

{=INDEX([DATA TO RETURN],MATCH([SHORT ENTRY],RIGHT([SOURCE COLUMN],LEN([SOURCE COLUMN])-1,0))}

Replace DATA TO RETURN with the column that contains the data you want to find, SHORT ENTRY with the reference with the missing first digit, SOURCE COLUMN the whole column with the full reference.

This is an array, so don't add the curly brackets. Once you've put the formula in, CTRL+SHIFT+ENTER to make it an array.

 

[auto.pilot]

Thank you.