Often you may want to calculate commissions for an employee based on their sales numbers.

For example, you might want to use the following table in Excel to determine the commission to pay an employee based on their total sales for the year:

Here is how to interpret this table:

- If an employee has sales between $0 < $5,000, they receive
**0%**commission. - If an employee has sales between $5,000 < $10,000, they receive
**2%**commission. - If an employee has sales between $10,000 < $20,000, they receive
**5%**commission. - If an employee has sales between $20,000 < $40,000, they receive
**8%**commission. - If an employee has sales > $40,000, they receive
**12%**commission.

The following example shows how to use a **VLOOKUP** function in Excel to determine the sales commission to pay an employee based on their total sales.

**Example: How to Calculate Commissions in Excel**

Suppose an employee has a total of $11,000 in sales for a given year.

We can type this sales amount into cell **E1** and then type the following formula into cell **E2** to determine their commission rate:

=VLOOKUP(E1, $A$2:$B$6, 2, TRUE)

The following screenshot shows how to use this formula in practice:

The formula returns a commission rate of** 5%** since the sales value of $11,000 fell between the range $10,000 and $20,000.

Suppose we change the sales value to $27,000:

The formula now returns a commission rate of** 8%** since the sales value of $27,000 fell between the range $20,000 and $40,000.

**How This Formula Works**

You can use the **VLOOKUP** function in Excel with **TRUE** as the last argument to look up a value that falls in a range and return the corresponding value in another range.

Note that the **VLOOKUP** function uses the following basic syntax:

**VLOOKUP (lookup_value, table_array, col_index_num, [range_lookup])**

where:

**lookup_value**: The value to look up**table_array**: The range of cells to look in**col_index_num**: The column number that contains the return value**range_lookup**: TRUE = look for approximate match, FALSE = look for exact match

By using a value of **TRUE** for the last argument, you can look up a value that falls in a range.

For example, when we looked up the value $11,000 in the commission table this exact value didn’t exist in the Sales column so the **VLOOKUP** function looked for the next largest value in the Sales column that was less than $11,000.

The formula identified the value of **$10,000 **and returned the commission rate that corresponded to this value, which was **5%**.

Note that the values in the Sales column are already sorted from least to greatest, which is why the **VLOOKUP** function was able to correctly identify the next largest value that was less than the lookup value.

If the first column in your lookup range is not sorted, the **VLOOKUP** function could return unexpected results.

**Additional Resources**

The following tutorials explain how to perform other common tasks in Excel:

Excel: Use VLOOKUP to Return Multiple Values Horizontally

Excel: Use VLOOKUP to Return Blank Instead of Zero

Excel: How to Use VLOOKUP by Date