How to calculate the discount rate in Excel with VLOOKUP? / Conditional discount calculation in Excel

We need to get discount rate for a particular OrderID according to order amount. Calculate the order amount with Discount Table number 2.

As we know in excel we put ‘=’ sign to start formula. We are going to use VLOOKUP to find the discount rate. Click on C5 and write =VLOOKUP(Lookup_value, Table_array, Col_Index_num, Range_lookup)

Write this in formula bar

=VLOOKUP(B5,Table1[#All],2,TRUE]

#TRUE : to find the closest match or FALSE : to find exact match.

Now drag with autofill ‘+’ at the right corner of the cell until C14.

Now as you can see discount rates are automatically entered with the help of VLOOKUP function and you don’t have to calculate each and every value manually.

Now will calculate Order amount with discount table which is beside the orders table i.e; Table2 .

For this first we need to calculate how much discount we got on particular ‘Order Amount’ by multiplying (Discount Rate * Order Amount) ,then will subtract this with ‘Order Amount’.

=Order Amount – (Order Amount * Discount Rate)

=B5 – (B5 * C5)

Just like above will autofill the remaining cells and will get the remaining discounted amounts.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s