Calculating with Vlookup

Using Vlookup in Excel to work out tax, fees or commission from a table by David Goodmanson

In part 1 we covered the basics of VLookup, let’s use it to calculate tax for a list of employees and their salaries. We’ve chosen this practical example because it contains a fixed amount plus a marginal percentage for excess above a threshold value – a common scenario for tax, fee and commission calculations.

Making numbers ready for Excel

In keeping with real world situations, imagine we’ve been given data in a form not readily compatible with an Excel spreadsheet. Figure 1 shows an Income Tax Rates table. Column A (Taxable Income) shows the tax brackets and the rate for each bracket. Column B (Tax on Income) shows the applicable tax rate and fixed tax for each bracket. As it stands, there is not much that Excel can do with this table. We need to transform the table into something Excel can use, and we can apply VLookup to.

 
Figure 1 – Tax Rate Data

What are the components of the Income Tax Rates table?

1. The tax brackets can be changed into thresholds that can be used by VLookup to find the tax level for a given income.
   
2. The Tax on Income column can be broken into 2 parts:
   
   
   
   1. Fixed tax, e.g. $4,200 for income between $34,000 and $80,000.
      
   2. Marginal tax rate, e.g. 30% for $34K to $80K

So, how do we put this into a format digestible by Excel and VLookup?

• Each tax bracket is a threshold. VLookup will use the threshold value to determine what tax rates apply. We will set the range_lookup value to true, so that VLookup finds an approximate match (i.e. it looks up a range of values). So the threshold values will form the all important column 1 of the Lookup table. (see figure 2).
  
• We need a column for the Fixed tax which applies to each bracket.
  
• And we need a column for the Marginal rate of tax that applies to each income threshold.

 
Figure 2 – Convert data to lookup table

So how does VLookup use column 1 of the lookup table, i.e. Threshold?

1. Threshold needs to be sorted in an ascending order.
   
2. VLookup, will return the closest Threshold value that is less than or equal to the lookup value, i.e. the salary value. So if a salary value was $5,999 what Threshold value would VLookup select? It would look for the closest Threshold value say $6,000? But, is $6,000 less than the lookup value of $5,999? No! So the closest value that is less than the lookup value is $0. So VLookup returns the $0 Threshold.

OK, so how Excel uses VLookup should be a little clearer. Now, one further step in setting up the lookup table is to name it as a range. So let’s name the table “TaxRates”, this will make it easier to refer to in our formulas. Highlight cells D2:F6 and on the Excel menu go to Insert/Name/Define, and type in TaxRates in the Names in Workbook box, click Add and then OK. See the named lookup table in figure 3.

 
Figure 3 – Name lookup table as TaxRates

Now we are ready to complete the Tax Calculation for the list of Northwind Employees. The Employees are listed in column A and their salary is shown in column B. For the calculation I have set up 2 tabular calculations.

1. As a stage by stage calculation (figure 4)
   
2. An advanced set up with a minimum of formulas (figure 5).

 
Figure 4 – Stage by stage calculation

Each formula is shown separately to identify the calculation in each cell. Column B of the Tax Calculation table yields the Threshold Amt that VLookup returns from column 1 of the named range “TaxRates” Column C returns the 2^nd column of “TaxRates” Fixed Amt and Column D returns the marginal rate of tax applicable to that salary. Column E determines the difference between Salary and the Threshold Amt and multiplies it by the relevant marginal tax rate. Finally, Column F simply adds the Fixed Tax to the Marginal Tax to give Total Tax. Once the formulas are set up, simply fill them down to complete the table.

Advanced Calculation

 
Figure 5 – Advanced Example

This table puts together what we have learnt into one example. Listed below in Figure 6 are the formulas used in this example. Create the TaxRates table list some salaries, then copy these formulas into Excel.

Formulas for Advanced Example

Fixed Tax
=VLOOKUP(B2,TaxRates,2,TRUE)

Marginal Tax
=(B2-VLOOKUP(B2,TaxRates,1,TRUE))* (VLOOKUP(B2,TaxRates,3,TRUE))

Total Tax
=(B2-VLOOKUP(B2,TaxRates,1,TRUE))* (VLOOKUP(B2,TaxRates,3,TRUE))+ VLOOKUP(B2,TaxRates,2,TRUE)

I’m grateful for the wonderful and supportive comments from Office Watch readers after the first part of this series – keep them coming. Next month I’ll look at the expert version of Vlookup – a combination of the INDEX and MATCH functions.

See Also

• Excel’s IF function
• Match and Index lookup in Excel
• Lookup from a list in Excel