VLOOKUP with two client rates

VLOOKUP with two client rates

Generic formula 

=VLOOKUP(client,rates,col,0)*hrs+VLOOKUP(client,rates,col,0)*hrs

Related formulas 

Calculate shipping cost with VLOOKUP

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

 

VLOOKUP with two client rates

Generic formula 

=VLOOKUP(client,rates,col,0)*hrs+VLOOKUP(client,rates,col,0)*hrs

Related formulas 

Calculate shipping cost with VLOOKUP

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