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 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
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
- 0 or false: For an exact match
- 1 or true: For an approximate match
For the case of looking for a client rate
In the case of two rates, you can use formulas based on two VLOOKUP functions while calculating the final charge in excel.
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:
Here, “prices” is the named range [J5:L9]
WORKING OF THE FORMULA
The above formula is composed of two VLOOKUP functions.
This LOOKUP formula is for calculating the multiplication of prices1 and rate1.
This LOOKUP formula is for calculating the multiplication of price2 and rate2.
Finally, both the results are added in the final step, which gives
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.