INDEX/MATCH using multiple worksheets with alphanumeric index value

USAOz

New member
Joined
Sep 23, 2017
Messages
16
Reaction score
0
Points
0
Excel Version(s)
2016
OK, I’ve finally bitten the bullet and am trying to use Index/Match in Excel 2016, mainly because the data set being referenced is not in numerical order and the value I want to find is alphanumeric so vlookup won’t cut it. To compound the issue, the data set being referenced is on a different worksheet in the workbook!

I have 2 worksheets – one labelled Contractors & Employees (this contains employee details and tax status), the other is a timesheet labelled Wages & Salaries (this contains the calculations of employees hours, wages and tax to be deducted.

I simply can’t get index/match to pull the correct value from Contractors & Employees worksheet into the Wages & Salaries worksheet.
Data validation from a list works but needs to be re[peated for each column relevant to the employee’s circumstances. I ideally want to eliminate that so that I can select an employees ID number (alphanumeric) from a validation list on the Wages & Salaries worksheet and have the remaining columns auto-fill the relevant data from the Contractors & Employees worksheet.
I’ve tried adapting many different formulae shown in tutorials on the web but none seem to address the issue of alphanumeric index on a different worksheet!
Help?

Thanks
 

Attachments

  • Index Match multi worksheet problem.xlsx
    11.1 KB · Views: 10
Excel 2016 (Windows) 32 bit
A
B
C
D
E
F
G
3
Employee
ID Number
Employee
Surname
Employee
Given Name
Employment
Status
ABN or TFN
Tax
Threshold
Hourly
Pay Rate
4
C1000=INDEX('Employees & Contractors'!C$3:C$6,MATCH('Wages & Salaries'!$A4,'Employees & Contractors'!$B$3:$B$6,0))=INDEX('Employees & Contractors'!D$3:D$6,MATCH('Wages & Salaries'!$A4,'Employees & Contractors'!$B$3:$B$6,0))=INDEX('Employees & Contractors'!E$3:E$6,MATCH('Wages & Salaries'!$A4,'Employees & Contractors'!$B$3:$B$6,0))
=INDEX('Employees & Contractors'!F$3:F$6,MATCH('Wages & Salaries'!$A4,'Employees & Contractors'!$B$3:$B$6,0))​
=INDEX('Employees & Contractors'!G$3:G$6,MATCH('Wages & Salaries'!$A4,'Employees & Contractors'!$B$3:$B$6,0))
=INDEX('Employees & Contractors'!H$3:H$6,MATCH('Wages & Salaries'!$A4,'Employees & Contractors'!$B$3:$B$6,0))​
5
B1000
=INDEX('Employees & Contractors'!C$3:C$6,MATCH('Wages & Salaries'!$A5,'Employees & Contractors'!$B$3:$B$6,0))​
=INDEX('Employees & Contractors'!D$3:D$6,MATCH('Wages & Salaries'!$A5,'Employees & Contractors'!$B$3:$B$6,0))​
=INDEX('Employees & Contractors'!E$3:E$6,MATCH('Wages & Salaries'!$A5,'Employees & Contractors'!$B$3:$B$6,0))​
=INDEX('Employees & Contractors'!F$3:F$6,MATCH('Wages & Salaries'!$A5,'Employees & Contractors'!$B$3:$B$6,0))​
=INDEX('Employees & Contractors'!G$3:G$6,MATCH('Wages & Salaries'!$A5,'Employees & Contractors'!$B$3:$B$6,0))​
=INDEX('Employees & Contractors'!H$3:H$6,MATCH('Wages & Salaries'!$A5,'Employees & Contractors'!$B$3:$B$6,0))​
6
T1000
=INDEX('Employees & Contractors'!C$3:C$6,MATCH('Wages & Salaries'!$A6,'Employees & Contractors'!$B$3:$B$6,0))​
=INDEX('Employees & Contractors'!D$3:D$6,MATCH('Wages & Salaries'!$A6,'Employees & Contractors'!$B$3:$B$6,0))​
=INDEX('Employees & Contractors'!E$3:E$6,MATCH('Wages & Salaries'!$A6,'Employees & Contractors'!$B$3:$B$6,0))​
=INDEX('Employees & Contractors'!F$3:F$6,MATCH('Wages & Salaries'!$A6,'Employees & Contractors'!$B$3:$B$6,0))​
=INDEX('Employees & Contractors'!G$3:G$6,MATCH('Wages & Salaries'!$A6,'Employees & Contractors'!$B$3:$B$6,0))​
=INDEX('Employees & Contractors'!H$3:H$6,MATCH('Wages & Salaries'!$A6,'Employees & Contractors'!$B$3:$B$6,0))​
7
C1000=INDEX('Employees & Contractors'!C$3:C$6,MATCH('Wages & Salaries'!$A7,'Employees & Contractors'!$B$3:$B$6,0))=INDEX('Employees & Contractors'!D$3:D$6,MATCH('Wages & Salaries'!$A7,'Employees & Contractors'!$B$3:$B$6,0))=INDEX('Employees & Contractors'!E$3:E$6,MATCH('Wages & Salaries'!$A7,'Employees & Contractors'!$B$3:$B$6,0))
=INDEX('Employees & Contractors'!F$3:F$6,MATCH('Wages & Salaries'!$A7,'Employees & Contractors'!$B$3:$B$6,0))​
=INDEX('Employees & Contractors'!G$3:G$6,MATCH('Wages & Salaries'!$A7,'Employees & Contractors'!$B$3:$B$6,0))
=INDEX('Employees & Contractors'!H$3:H$6,MATCH('Wages & Salaries'!$A7,'Employees & Contractors'!$B$3:$B$6,0))​
Sheet: Wages & Salaries
 
Back
Top