Tech School Solutions

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.