# Index and Match with Wildcards

1. ## 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

2. ## Re: Index and Match with Wildcards

Try this, copied down and across...
=IFERROR(INDEX(Sheet2!\$B\$3:\$E\$500,MATCH("*"&\$B4,Sheet2!\$C\$3:\$C\$500,0),MATCH(Sheet1!C\$3,Sheet2!\$B\$3:\$E\$3,0)),"")

3. ## Re: Index and Match with Wildcards

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

4. ## Re: Index and Match with Wildcards

Cant see what you had before, sorry If you show me, I may be able to see.

Happy it worked for you, and thanks for the feedback

5. ## Re: Index and Match with Wildcards

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.

6. ## Re: Index and Match with Wildcards

OK well shout if you need more help

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1