Is it best practice to add helper columns or is there a better way to vlookup?

Copper Contributor

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.

2 Replies
What is your Excel version? Using Microsoft-365 dynamic formulas this can be simplified. Share a sample workbook and show your desired output.

@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.