Feb 14 2023 01:35 AM
I need to lookup pay rate exceptions from a separate sheet. On the separate sheet, I have four tables:
1. Client names, days of the week, and job type
2. Second table with days of the week, job type and location
3. Third table with Days of the week and job type
4. Fourth table for Standard rates including days of the week and job type
How I have done this is a macro creates three new tables, creating combinations of:
1. Combo 1: Client + Days of the week + Job Type
2. Combo 2: Days of the week + Job Type + Location
3. Combo 3: Days of the week + job type
I have also added these combo columns on the separate sheet, and am using a vlookup with nested if to check in hierarchy of Table 1, then 2, then 3, and if it matches none of the exceptions then table 4 Standard rates.
The result is a very long if statement with many vlookups which if it matters looks kind of like this:
=IF([@Combined]=IFERROR(VLOOKUP([@Combined],Table 1,1,0),0), IFERROR(VLOOKUP([@Combined],Table 1,7,0),0),IF([@[Combined_ rate_job_location]]=IFERROR(VLOOKUP([@[Combined_ rate_job_location]],Table 2,1,0),0),IFERROR(VLOOKUP([@[Combined_ rate_job_location]],Table 2,6,0),0),IF([@[Combined_ rate_job_std]]=IFERROR(VLOOKUP([@[Combined_ rate_job_std]],Table 3,1,0),0),IFERROR(VLOOKUP([@[Combined_ rate_job_std]],Table 3,6,0),0),IF([@[Combined_ rate_job_std]]=IFERROR(VLOOKUP([@[Combined_ rate_job_std]],Table 4,1,0),0),IFERROR(VLOOKUP([@[Combined_ rate_job_std]],Table 4,6,0),0),10.1))))"
It works fine but I want to make sure I'm following best practices and I'm not sure if there is a better way to do this. I tried to use AND statements but it ended up looking even more complicated. So I'm just looking for advice to make sure I'm learning the right way.
Feb 14 2023 02:07 AM
Feb 14 2023 02:46 AM
@Harun24HR Hi I am on Excel 365.
I've attached a demo file. The exception sheet is a separate sheet in my actual case and there is a macro that runs to create sheet 3. I am wondering if I am following the best practice method to achieve this.