Here is our scenario. We have at database of purchases to which we need to apply a discount. The discount applied depends on the product category: A, B or C and the quantity purchased. The lookup tables for each category are on separate sheets as shown below.
5 FREE EXCEL TEMPLATES
Plus Get 30% off any Purchase in the Simple Sheets Catalogue!
We are going to achieve this using a VLOOKUP formula that employs the INDIRECT function to reference the relevant worksheet.
First of all let’s look at the syntax that Excel uses to refer to cells on another worksheet. The worksheet name has an apostrophe in front of it and an apostrophe and exclamation mark after it.
='CAT A'!$A$2:$B$6
The INDIRECT function is useful in this context as it returns the cell/range reference specified by a text string. In other words we can refer to the relevant sheet name by referring to the category letter in column B of the Transactions sheet. The category letter on its own won’t suffice but if we concatenate the letter with the rest of the syntax necessary to refer to the sheet, we have a solution.
=INDIRECT("'""CAT "B2"'!$A$2:$B$6")
Therefore the formula we are going to use in the Discount column of the Transactions sheet is as follows:
=VLOOKUP(C2,INDIRECT("'"&"CAT "&B2&"'!$A$2:$B$6"),2)
And here are the results…
The discounted price calculation would be
=C2*D2*(1-F2)
giving these results…
