This tutorial shows how to calculate VLOOKUP with two client rates in Excel using the example below;
To lookup two different rates for the same client, and calculate a final charge, you can use a formula based on two VLOOKUP functions.
In the example shown, the formula in E5 is:
where “rates” is the named range G5:I8.
How this formula works
This formula is composed of two lookups for the same client. The first lookup finds the onsite rate for the client in column B and multiplies the result by the number of hours in column C:
The second lookup finds the offsite rate for same client and multiplies the result by the number of hours in column D:
In the final step the two results are added together: