VLOOKUP with two client rates

Often, you have to find the charge to be paid by a client. This task may look easy but when you have to do this for a large number of clients it becomes tedious. Let us consider a case, if there are 1000 clients and if there is more than one rate on these clients. In such a situation, VLOOKUP function can be very useful.  After the tutorial, you’ll be able to find the final charge with two client rates very easily even if there is a long list of clients.

VLOOKUP FUNCTION

VLOOKUP FUNCTION is an inbuilt function of excel. Whenever a VLOOKUP function is called in Excel, it searches for a lookup value. The lookup value is mostly the leftmost column of any section which is called a table array in a spreadsheet. The ‘V’ of the VLOOKUP function refers to vertical search.

SYNTAX OF VLOOKUP FUNCTION

=VLOOKUP(lookup_value,table_array,column_index,[range_lookup]

Where:

lookup value: the value that we are searching for

table_array: all the cells that might include the value

column_index: the column index on the table 

range_lookup: (optional)

  •  0 or false: For an exact match
  • 1 or true:  For an approximate match
  •  

For the case of looking for a client rate

=VLOOKUP(client,price,col,0)*rates

In the case of two rates, you can use formulas based on two VLOOKUP functions while calculating the final charge in excel.

FORMULA

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

EXAMPLE

Figure 1. Example of using VLOOKUP function to calculate the charge of two clients

While finding the final charge of a client having two rates, you can use the above formula which is based on two VLOOKUP functions used collectively.

In the above example, it is shown that the formula in E5 is:

=VLOOKUP(B5,prices,2,0)*C5+VLOOKUP(B5,prices,3,0)*D5

Here, “prices” is the named range [J5:L9]

WORKING OF THE FORMULA

The above formula is composed of two VLOOKUP functions.

Formula 1=VLOOKUP(B5,prices,2,0)*C5

This LOOKUP formula is for calculating the multiplication of  prices1 and rate1.

Formula 2=VLOOKUP(B5,rates,3,0)*D5

This LOOKUP formula is for calculating the multiplication of price2 and rate2.

Finally, both the results are added in the final step, which gives =(69*5)+(67*2)=479

Similarly, we can use the above formula for other clients by dragging the formula. This will make our work easier.

In this way, by using the VLOOKUP formulas, you can easily find the total charge on a client even if there are two rates to be charged for different prices on the same client. We hope that you won’t be facing any problem regarding this in the future.

Our customers love us!
“The expert was absolutely amazing and stuck with me the whole way through. They were polite, patient, seemed to want to genuinely help me and provided a solution that I would never have managed otherwise. I could not be more thankful for their support and solution. Thank you!” - - Chris T, in California

Leave a Comment

avatar