## Use VLOOKUP to calculate discount percentages

Have you ever tried to build a formula to calculate discounts depending on price? The VLOOKUP functionÂ is much easier to use than nested IF functions.

You can also use the methods described in this article to calculate commissions, tariffs, charges, shipping costs,Â packaging expenses etc.

#### Table of contents

- Use price ranges to determine discount
- Linear discounts
- Multiple tables
- Two-way lookup in multiple tables
- Download xlsx file
- Functions in this post

### Use price ranges to determine discount

For this example our price range plan looks like this:

- Total price between 0 and 100 returns a 5% discount
- Total price between 100 and 300 returns a 7% discount
- Total price between 300 and 500 returns a 8% discount
- Total price between 500Â and 1000 returns a 10% discount
- Total price between 1000Â and aboveÂ returns a 15% discount

The picture below shows you the discount percentage table (B2:D7) and a summary table (B9:E19) with a calculated discount (E21).

The table above shows you the total amount of all items $1 332.60 in cell E20, the formula in cell E21 calculates a discount percentage. SinceÂ $1 332.60 is larger than $1 000 the formula returns 15%.

Cell E23 returns the discounted total.

Keep in mind, the values in the first column (B3:B7) must be sorted ascending. The third column (D3:D7) is not necessary for the formula calculation.

**Formula in cell E21:**

**Formula in cellÂ E22:**

**Formula in cell E23:**

The following chart shows you the different discount ranges from table above.

Read on to learn more about linear discounts.

### Linear discounts

In this scenario we want to use a linear equation to calculate the discount percentage. It begins on 0 (zero) with 2% and climbs up to 15% on $2 000, shown in cell range B3:C4 on the picture below.

These four numbers are our two coordinates. A coordinate in a plane has two values, x and y.Â You can draw a line between two coordinates thus a linear equation.

**Formula in cell E18:**

You can verify the calculated discount value by looking at this chart.

#### What is the formula above doing?

A linear equation looks like this: y=kx+m where k and m are constants. k is the slope of the line and m is the y-coordinate where the line crosses the y-axis.

A linearÂ equation consists of two coordinates, in this case (0,0.02) and (2000,0.15).

k is (y2-y1)/(x2-x1) or using cell refsÂ (C4-C3)/(B4-B3), x is cell E17.

m equalsÂ y if x is zeroÂ or using cell refs C3-((C4-C3)/(B4-B3))*B3

y = kx + m becomes Â (C4-C3)/(B4-B3)*E17+C3-((C4-C3)/(B4-B3))*B3

### Multiple tables

This example demonstrates how to use two different tables. The discount percentage depends on the category, the first table is category A and the second table is category B.

**Formula in cell G11:**

#### Explaining function in cell G11

You can easily follow the formula calculations, step by step.

- Select cell G11
- Go to tab "Formulas" on the ribbon
- Click "Evaluate formula" button

Click on "Evaluate" button to see next step in calculation, click OK when done.

**Step 1 - Identify relative position of category in array**

MATCH(F11,{"A","B"},0)

becomes

MATCH("B",{"A","B"},0)

and returns 2. B is the second number in the arrayÂ {"A","B"}. In other words, the MATCH function converts the category to a number. The number determines which range to be used.

**Step 2 - Choose cell range**

INDEX(($B$4:$C$7,$E$4:$F$7),,,MATCH(F11,{"A","B"},0))

becomes

INDEX(($B$4:$C$7,$E$4:$F$7),,,2)

and returnsÂ $E$4:$F$7. Â The INDEX function returns cell rangeÂ $B$4:$C$7Â or $E$4:$F$7 depending on the category. AnÂ entire range is returned because both row and column parameters are omitted.

**Step 3 - Use cell range in VLOOKUP function and return corresponding discount percentageÂ **

VLOOKUP(E11,INDEX(($B$4:$C$7,$E$4:$F$7),,,MATCH(F11,{"A","B"},0)),2,TRUE)

becomes

VLOOKUP(E11,$E$4:$F$7,2,TRUE)

becomes

VLOOKUP(1200,$E$4:$F$7,2,TRUE)

and returns 7%.Â The VLOOKUP function uses the selected range to retrieve the discount value from column 2 in the second table. Make sure the leftmost column in the tables are sorted descending.

Tip! This post explains how to use multiple conditions in a VLOOKUP function:

Use multiple conditions in Vlookup

### Two-way lookup in multiple tables

This example shows you how to do lookups in multipleÂ tables.

**Formula in cell S3:**

#### Explaining formula in cell S3

The INDEX function allows youÂ to choose betweenÂ different cell ranges depending on the region value in cell R3, if a value is present in the optional parameter area_num.

You can easily follow the formula calculations, step by step.

- Select cellÂ S3
- Go to tab "Formulas" on the ribbon
- Click "Evaluate formula" button

Click on "Evaluate" button to see next step in the calculation, click OK when done.

**Step 1 - Identify which cell range to fetch data from, C4:G8 or J4:N8**

The MATCH function lets you find out where in an array a value is located (relative position).

INDEX(($C$4:$G$8,$J$4:$N$8),,,MATCH(R3,{"North","South"},0))

becomes

INDEX(($C$4:$G$8,$J$4:$N$8),,,1,0))

and returns the first cell referenceÂ $C$4:$G$8.

**Step 2Â - Identify which cell range to search in, B4:B8 or I4:I8**

INDEX(($B$4:$B$8,$I$4:$I$8),,,MATCH(R3,{"North","South"},0))

becomes

INDEX(($B$4:$B$8,$I$4:$I$8),,,1)

and returnsÂ $B$4:$B$8

**Step 3Â -Â Match value "D" in cell rangeÂ $B$4:$B$8**

MATCH(Q3,INDEX(($B$4:$B$8,$I$4:$I$8),,,MATCH(R3,{"North","South"},0)),0)

becomes

MATCH(Q3,$B$4:$B$8,0)

becomes

MATCH("D",$B$4:$B$8,0)

and returns 4. Now we now the row number to use.

**Step 4Â -Â Find column number**

The steps to look for the column number is almost identical to finding the row number.

MATCH(P3,INDEX(($C$3:$G$3,$J$3:$N$3),,,MATCH(R3,{"North","South"},0)),0)

becomes

MATCH(P3,INDEX(($C$3:$G$3,$J$3:$N$3),,,1),0)

becomes

MATCH(P3,$C$3:$G$3,0)

becomes

MATCH("G",$C$3:$G$3,0)

and returns 2.

**Step 5 - Return value from intersection of a row and column number**

INDEX(INDEX(($C$4:$G$8,$J$4:$N$8),,,MATCH(R3,{"North","South"},0)),MATCH(Q3,INDEX(($B$4:$B$8,$I$4:$I$8),,,MATCH(R3,{"North","South"},0)),0),MATCH(P3,INDEX(($C$3:$G$3,$J$3:$N$3),,,MATCH(R3,{"North","South"},0)),0))

becomes

INDEX($C$4:$G$8,4,2)

and returns 63.

You can easily modify the formula to search simultaneously in 4Â different cross reference tables.

Example:

**Formula in cell S3:**

Tip! Read this post to doÂ Reverse two-way lookup in a cross reference table.

### Download excel *.xlsx file

VLOOKUP discount percentagesv2.xlsx

### Functions in this post:

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

Looks for a value in the leftmost column of a table, and then returns a value in the same row from a column you specify. By default, the table must be sorted in an ascending order.

**INDEX(**array,row_num,[column_num],[area_num]**)**

Returns a value or reference of the cell at the intersection of a particular row and column, in a given range

**MATCH(**lookup_value, lookup_array, [match_type]**)**

Returns the relative position of an item in an array that matches a specified value

Geoff asks: Hi Oscar, I have a cross reference table we use for shift scheduling. The x-axis is comprised of […]

How to perform a two-dimensional lookup

Question: How would I go about looking up data in a cross-reference table. I have the header row (i.e. 24) […]

Gets a value in a specific cell range based on a row and column number.

The table of contents below lets you quickly navigate to the formula you are looking for. The excel 2016 formula […]

VLOOKUP with multiple criteria

The VLOOKUP function cell D16 looks for both a value in column B and another value in column C. […]

How to return a value if lookup value is in a range

In this article, I will demonstrate four different formulas that allow youÂ to lookup a value that is to be found […]

VLOOKUP of three columns to pull a single record

Question: Does anyone know how to do a VLOOKUPÂ of three columns to pull a single record? Answer: Array formula in […]

How to use the VLOOKUP function

The VLOOKUP function lets you search the leftmost column for a value and return another value on the same row […]

### One Response to “Use VLOOKUP to calculate discount percentages”

### Leave a Reply

### How to comment

**How to add a formula to your comment**

<code>Insert your formula here.</code>

**Convert less than and larger than signs**

Use html character entities instead of less than and larger than signs.

< becomes < and > becomes >

**How to add VBA code to your comment**

[vb 1="vbnet" language=","]

Put your VBA code here.

[/vb]

**How to add a picture to your comment:**

Upload picture to postimage.org or imgur

Use the img tag, like this: <img src="Insert pic link here">

**Contact Oscar**

You can contact me through this contact form

[…] https://www.get-digital-help.com/2017/05/31/use-vlookup-to-calculate-discount-percentages/ […]