INDEX/VLOOKUP to match and check against another worksheet

[vbathehardway]

I'm a bit of an Excel noob so bear with me here. I have the following abridged sheet:

Sheet1
              H                               AP                    AO
    1 Transaction Description             Employee Name              Type
    2 ER 12345678                             blank                  blank
    3 ER 13182984                             blank                  blank
    4 ER 18213289                             blank                  blank
    5 ER 13829429                             blank                  blank
    6 ER 89234024                             blank                  blank

And another sheet in the same file to reference the names against:

Sheet2
                E                                         I
    1 Expense Report Number                    Employee Name     
    2 12345678                                    Chris Rock         
    3 13182984                                    Hank Hill          
    4 18213289                                    Tom Sawyer         
    5 13829429                                    Elon Musk          
    6 89234024                                    Tupac Shakur

And I was wondering how to efficiently fill in the first excel sheet's Employee Name and Type columns from the matching report number of the second sheet as such:

Sheet1
           H                                          AP                        AO
    1 Transaction Description                  Employee Name                      Type   
    2 ER 12345678                              Chris Rock                           A
    3 ER 13182984                             Hank Hill                             A
    4 ER 18213289                             Tom Sawyer                            A
    5 ER 13829429                             Elon Musk                             A
    6 ER 89234024                             Tupac Shakur                          A

My attempt so far:

=INDEX(EmployeeName,MATCH(--MID(H2,4,8),ExpenseReportNumber,0))
<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;">
</code>


But this returns a N/A value. Any help is much appreciated

 

[Special-K99]

in Sheet1!AP1
=VLOOKUP(MID(H2,4,8),Sheet2!E$2:I$1000,5,0)

If this doesn't work then try this
=VLOOKUP(MID(H2,4,8)+0,Sheet2!E$2:I$1000,5,0)
NOTE: The +0 above.

You haven't explained where the Type information comes from.
It doesn't appear in either of your first two tables.

 

[vbathehardway]

Thanks for your reply Special-K99, the Type information is predetermined and will always be 'A'