There is a better way to handle this, and it starts with rethinking how you treat activity codes in your timesheet data. The real issue isn't a lack of Excel skill, it's that your current setup forces a manual step that shouldn't exist. You've already identified the core problem: your Index-Match formula only looks at last names, which works fine for employees with one position but breaks the moment someone splits their time across multiple roles. The activity code is the missing key, and it's right there in your data, waiting to be used.
The practical fix is to make your lookup formula reference both the employee's last name and the activity code together. Instead of relying on a single match, you can combine the two criteria into a unique identifier that points directly to the correct pay rate in your Comp Rate Reference Table. For example, if your reference table lists each position with its own activity code, you can create a helper column that concatenates the last name and activity code, then use that as your lookup value. This way, the formula automatically pulls the right rate every time, no manual highlighting or editing required. It's a small change to your setup, but it turns a fragile workaround into a repeatable process.
What's striking about your situation is that you've already diagnosed the inefficiency, you know the activity code is the differentiator, and you're currently using conditional formatting just to spot it visually. That's a smart instinct, but it stops short of automation. The next step is to trust the data itself to do the work. By incorporating the activity code into your lookup logic, you're not just fixing one invoice; you're building a system that scales. When you have five employees with multiple positions, or when someone changes roles mid-year, the formula adapts without you having to trace through each row.
The takeaway here is that you don't need a more complicated formula, you need a more complete one. Your current Index-Match is close, but it's only looking at half the story. Add the activity code as a second condition, and you'll eliminate the manual step entirely. Start by adding a helper column to your timesheet that combines the last name and activity code, then do the same in your reference table. The match becomes exact, and the pay rate fills in automatically. It's a simple adjustment that respects the complexity of your real-world workflow, and it means you can stop babysitting the spreadsheet and start trusting it.