Exceljet

Quick, clean, and to the point

VLOOKUP with two client rates

Excel formula: VLOOKUP with two client rates
Generic formula 
=VLOOKUP(client,rates,col,0)*hrs+VLOOKUP(client,rates,col,0)*hrs
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.

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
Related formulas 
Excel formula: Calculate shipping cost with VLOOKUP
Calculate shipping cost with VLOOKUP
The core of the formula is VLOOKUP, which is configured in approximate match mode by setting the fourth argument to 1 or TRUE. In approximate match mode, the values in the first column of VLOOKUP must be sorted. VLOOKUP will return a value at the...
Related functions 
Excel VLOOKUP function
Excel VLOOKUP Function

VLOOKUP is an Excel function to look up data in a table organized vertically. VLOOKUP supports approximate and exact matching, and wildcards (* ?) for partial matches. Lookup values must appear in the first...

Related videos 
How to use VLOOKUP
In this video, learn how to use VLOOKUP to build a simple form to extract employee information from a table, in about 3 minutes. VLOOKUP is a key Excel function.

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

500 Formulas | 101 Functions

Download 200+ Excel Shortcuts

Get over 200 Excel shortcuts for Windows and Mac in one handy PDF.