VLOOKUP with two client rates

Summary

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.

Generic formula

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

Explanation

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

Author

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.

Exceljet.net is a really good resource and has helped me numerous times move from a beginner Excel user to a high end intermediate user.

Isaac

More Testimonials

Get Training

Quick, clean, and to the point training

Learn Excel with high quality video training. Our videos are quick, clean, and to the point, so you can learn Excel in less time, and easily review key topics when needed. Each video comes with its own practice worksheet.

View Paid Training & Bundles