INDEX/VLOOKUP to match and check against another worksheet

vbathehardway

New Member
Joined
Jun 12, 2017
Messages
7
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
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top