Forum Discussion
auditing rates
I was wondering if anyone can help me verify the accuracy of union hourly rates from employee data entered on spreadsheet ABC, against my legend which is located on spreadsheet XYZ, (within the same workbook) by using a formula(s) in excel? i want to color code the ones that are incorrect.
examples:
my legend:
Department Job Title Hourly Rate
Production Level 1 $15.90
Warehouse Level 1 $17.00
Production Level 2 $17.00
Sanitation Level 1 $17.00
Warehouse Level 2 $18.00
Production Level 3 $18.00
Sanitation Level 2 $18.00
Production Lead $19.00
Production Shift Lead $24.00
headers of my employee data on spreadsheet ABC:
EEID DEPARTMENT JOB TITLE RATE
i have experimented with vlookup, xlookup, if statements and IFS statement. it just became to much for me. any help will be greatly appreciated. Thank you for your help.
7 Replies
In general something like
=XLOOKUP(Department&JobTitle, XYZ!Department&XYZ!JobTitle, XYZ!Rate)shall work
- this formula is not taking into consideration my range of that information to compare it to on the spreadsheet ABC to compare my legend against.
when i type in this formula i get an error message #NAME?.Perhaps you may share small sample file or at least screenshot to illustrate how your data is structured exactly.
