Index and Match with Wildcards

Good morning all,

Ok so once again i am sure my issue can be solved with a good use of match and index (starting to learn them slowly).

Essentially what I am trying to do today is match a static list of employee numbers (The list will grow over time though), with an employee number in the middle of an array in another spreadsheet. The issue being - the employee numbers in the second spreadhseet generally have a random collection of numbers and letters before the 6 digit number. So for example - Employee number sheet 1 is 123456 - sheet 2 could be gt00123456.

But the purpose of matching the numbers is to find the matching values to fill columns. Since i am terrible at explaining things, ive attached a spreadsheet example.

Couple notes - From what i can tell the sheet 2 employee numbers seem to have last 6 digits as the actual employee numbers so possibly use of "right" instead of wildcard?
The 2nd sheet will be updated every couple weeks or month / this is just a static dump from a downloaded spreadsheet (unfortunately due to the private nature of data i can only get limited access). The column names should still match though.

Appreciate any assistance you guys can give.

Cheers,

Pytheus

Works great, thanks! I was so close to that though... Cant actually see what i was doing wrong.

Nah just random forumlas i had tried earlier. What you wrote looks really simialr to what i had tried - probably had the right stuff in various combinations of the wrong formulas. Now just the effort of making the formula work with these excel files -.-v Got 3 different excel files from 3 different areas and i have to make them talk without making any changes if possible. This is why i prefer using Cognos - all this is already done for me lol.