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

 

[FDibbins]

It's hard to make out where 1 set ends and the next starts, but assuming your data is something like this...
sheet1
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]blah 1[/td][td]Blah 2[/td][td]blah 3[/td][/tr]

[tr][td]

2​

[/td][td]

1​

[/td][td]

10​

[/td][td]

100​

[/td][/tr]

[tr][td]

3​

[/td][td]

2​

[/td][td]

20​

[/td][td]

200​

[/td][/tr]

[tr][td]

4​

[/td][td]

3​

[/td][td]

30​

[/td][td]

300​

[/td][/tr]

[tr][td]

5​

[/td][td]

4​

[/td][td]

40​

[/td][td]

400​

[/td][/tr]

[tr][td]

6​

[/td][td]

5​

[/td][td]

50​

[/td][td]

500​

[/td][/tr]

[tr][td]

7​

[/td][td]

6​

[/td][td]

60​

[/td][td]

600​

[/td][/tr]

[tr][td]

8​

[/td][td]

7​

[/td][td]

70​

[/td][td]

700​

[/td][/tr]

[tr][td]

9​

[/td][td]

8​

[/td][td]

80​

[/td][td]

800​

[/td][/tr]

[tr][td]

10​

[/td][td]

9​

[/td][td]

90​

[/td][td]

900​

[/td][/tr]

[tr][td]

11​

[/td][td]

10​

[/td][td]

100​

[/td][td]

1000​

[/td][/tr]
[/table]
and you want this...
sheet2
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][/tr]
[tr][td]

1​

[/td][td]blah 1[/td][td]Blah 2[/td][td]blah 3[/td][/tr]

[tr][td]

2​

[/td][td]

1​

[/td][td]

10​

[/td][td]

100​

[/td][/tr]

[tr][td]

3​

[/td][td]

3​

[/td][td]

30​

[/td][td]

300​

[/td][/tr]

[tr][td]

4​

[/td][td]

7​

[/td][td]

70​

[/td][td]

700​

[/td][/tr]
[/table]
with your criteria in column A
B2=IFERROR(INDEX(Sheet1!$A$2:$C$11,MATCH(Sheet2!$A2,Sheet1!$A$2:$A$11,0),MATCH(Sheet2!B$1,Sheet1!$A$1:$C$1,0)),"")
copied down and acrosss as needed