Use case: Basic Tax Calculation using Lookup Tables

The tax brackets can be confusing to work with. Different ‘levels’ of your income needs to be multiplied with a different percentage to find the total. When you want to do tax calculations in Zapier, you are limited with regular IF function. You need to nest IF functions for each tax level that makes the formula long and hard to read.

Alternatively, you can use a SpreadsheetWeb Hub application to create a table using a single Excel-based lookup function to calculate tax amounts.

Note: Zapier do has lookup table support as well. However, it is hard to create and maintain due to website capabilities and also limited with exact match option. In other words, Zapier's lookup table cannot return a value when you search for an amount between two tax brackets.


Our sample is based on tax brackets and fictitious basic tax rates below:

Low High Rate
0 9,999 7%
10,000 24,999 10%
25,000 49,999 14%
50,000 99,999 22%
100,000 249,999 30%
250,000 and over 45%

For example, if your salary is 150,000, you will pay 150,000 * 30% = 45,000 in taxes.

Zapier Approach

As mentioned before, you need nested IF functions to calculate basic tax rate of your income. Zapier's spreadsheet-style formulas can be found in their Formatter app. Select Numbers as Event.

You need to select Spreadsheet-Style Formula option for the Transform property in the Set-up action step. Once selected, you can see a textbox where you can enter your formula.

As you can see in the screenshot and the formula at below, evaluating a tax table with 6 rows requires 5 IF functions to be nested. Also, we need to mention that you have to move your hand from keyboard and use your mouse to add a dynamic field, e.g., Salary in our sample.

IF({{1. Salary}}<10000,0.07,IF({{1. Salary}}<25000,0.1,IF({{1. Salary}}<50000,0.14,IF({{1. Salary}}<100000,0.22,IF({{1. Salary}}<250000,0.3,0.45)))))

Here is the tax rate for 50,000:

SpreadsheetWeb Hub App Approach

With SpreadsheetWeb Hub, you are not limited with IF function. You can use any lookup function of your choice. In the next section you can find how to build your tool in Excel. If you already have a model in an Excel workbook, you can skip to the App creation section directly.

Preparing model in Excel

First, create a table that contains lower limits and tax rates. (B5:C10)

Limit Rate
0 7%
10,000 10%
25,000 14%
50,000 22%
100,000 30%
250,000 45%

Then, enter the value you want to calculate its tax into a cell (F4).

Here are both XLOOKUP and VLOOKUP versions of tax calculation:

Finally, give names into cells that you want to use on your Zaps. For example, the value you want to calculate its tax (F4) and the tax value (F7).

Creating a SpreadsheetWeb Hub Application

To create your application, login your SpreadsheetWeb Hub account and click Create New Application button from the Applications page.

Once your Excel file is uploaded, you will be directed to the Applications page. Unless you want to generate a custom user interface for your app, you can continue with Zapier.

Note: To create your custom web application, use Go to Designer button to open the Designer module and start building.

SpreadsheetWeb Hub in Zapier

In Zapier, choose the application as SpreadsheetWeb Hub app and Calculate as the event to calculate basic tax rate according to amount in previous steps.

Login to your SpreadsheetWeb Hub account if you haven't already and choose your account.

Choose your workspace and application to populate the named ranges in your workbook. For example, we named the cell F4 as "Income" which appears as an input box. Bind the value you want to calculate its tax to "Income" and select the named range of the cell that contains the formula ("TaxRate").

You are ready to go:

Zapier supports spreadsheet-like formulas, but it has limitations. When it comes to work with tax calculations, Zapier’s spreadsheet-like formulas might not be enough for businesses that require more complex calculations with dependent cells that are easier to build and maintain in spreadsheets. With SpreadsheetWeb Hub, businesses can create their tax calculation tools without stumbling into Zapier’s limitations and include such complex spreadsheet calculations into their Zapier automations.


Zapier Formatter:

SpreadsheetWeb Hub:


PS: SpreadsheetWeb is not a financial consultant company and we do not provide any assistance for tax or personal finance purposes. The methods you will see in this article should only give you ideas about how to build similar tools on your own using SpreadsheetWeb Hub platform. The data used in this article was retrieved at the of its publishing and may not reflect the current rates.