Lookup & Reference
Billing Lookup with Standard vs. Partner Client Rates (XLOOKUP + IF)
Calculate billable timecard line totals by selecting between Standard and Retainer/Partner hourly rate columns in a role rate card via XLOOKUP and multiplying by hours logged.
Eliminating duplicate rate cards when clients split into Standard vs. Retainer pricing!
Law firms, engineering consultancies, and creative agencies bill the same role catalog (Principal, Senior Dev, QA Analyst) under two fee schedules: Standard Hourly Rates (column F) and Preferred Retainer Rates (column G). Instead of maintaining two separate lookup tables, store both columns side by side on one rate card and let IF(Client_Tier = "Retainer", XLOOKUP(Role, E2:E4, G2:G4), XLOOKUP(Role, E2:E4, F2:F4)) (or VLOOKUP with IF(Tier="Retainer", 3, 2)) pull the exact applicable hourly rate!
Multiplying that resolved rate by billable hours C2 computes the line fee in one step—while catching unrecognized role codes cleanly before they corrupt invoice totals!
| Timecard Entry (Role | Tier | Hours) | Standard Rate (F) | Retainer Rate (G) | Billed Line Total |
|---|---|---|---|
| Senior_Dev | Retainer | 12.5 hrs | $180 / hr | $150 / hr (Selected) | $1,875.00 |
| Senior_Dev | Standard | 12.5 hrs | $180 / hr (Selected) | $150 / hr | $2,250.00 |
| Unknown Role "Intern" | Retainer | Not in E2:E4 | #N/A Trapped | "Role Not in Rate Card" |
Two-Tier Client Rate Line Total (Hours × Applicable Rate)
Selects Retainer rate column G2:G4 when B2 = "Retainer" or Standard rate column F2:F4 otherwise, multiplying by logged hours C2.
=IF(C2 < 0, "Invalid Hours", IFNA(ROUND(C2 * IF(B2 = "Retainer", XLOOKUP(A2, $E$2:$E$4, $G$2:$G$4), XLOOKUP(A2, $E$2:$E$4, $F$2:$F$4)), 2), "Role Not in Rate Card"))Effective Hourly Rate Applied ($/hr)
Returns the unit hourly rate pulled for role A2 and client pricing tier B2.
=IFNA(IF(B2 = "Retainer", XLOOKUP(A2, $E$2:$E$4, $G$2:$G$4), XLOOKUP(A2, $E$2:$E$4, $F$2:$F$4)), "Role Not in Rate Card")Try the example
Use the original values or change them to test your understanding.