VLOOKUP with two client rates in Excel
This tutorial shows how to calculate VLOOKUP with two client rates in Excel using the example below;
Formula
=VLOOKUP(client,rates,col,0)*hrs+VLOOKUP(client,rates,col,0)*hrs
Explanation
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:
=VLOOKUP(B5,rates,2,0)*C5+VLOOKUP(B5,rates,3,0)*D5
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:
=VLOOKUP(B5,rates,2,0)*C5
The second lookup finds the offsite rate for same client and multiplies the result by the number of hours in column D:
VLOOKUP(B5,rates,3,0)*D5
In the final step the two results are added together:
=(50*0)+(60*16) =960