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

	A​	B​	C​
1​	blah 1	Blah 2	blah 3
2​	1​	10​	100​
3​	2​	20​	200​
4​	3​	30​	300​
5​	4​	40​	400​
6​	5​	50​	500​
7​	6​	60​	600​
8​	7​	70​	700​
9​	8​	80​	800​
10​	9​	90​	900​
11​	10​	100​	1000​

and you want this...
sheet2

	A​	B​	C​
1​	blah 1	Blah 2	blah 3
2​	1​	10​	100​
3​	3​	30​	300​
4​	7​	70​	700​

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