Hi guys. I need some help with utilizing excel functions. Specifically, vlookup, concantenate and iferror. This is the question that is eluding me.
"The Preferred Purchaser Discount % (heading “Preferred Purchaser Discount %”) is allowed to certain favoured customers of the business. These customers are listed in the Preferred Customer Table (see Data_Table worksheet). The indexing in this table is the concatenation of three values: customer name, the underscore character, and the customer suburb (e.g., Smith_Aspley). You must retrieve the appropriate preferred customer discount rate from this table. You must also recognise that only a small percentage of customers are given “preferred” status. This means that a lookup of the table may not always return a positive result. You will develop a formula combining VLOOKUP, CONCATENATE, and IFERROR. You must use the appropriate named range within your VLOOKUP function. The resulting data should be formatted as Percentage with zero decimal places."
The final solution(utilizing the excel function mentioned above) will be on the purchase table under "preferred purchaser discount %." These two tables are in separate sheets. Data table is sheet 1 and purchaser table is sheet 2 on excel 2013. If anyone can help me find a solution on how to complete this particular question I will be very grateful. Thank you in advance.
Below is the tables that are relevant to this question.
Data table
<colgroup><col span="2"></colgroup><tbody>
</tbody>Purchaser table
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
"The Preferred Purchaser Discount % (heading “Preferred Purchaser Discount %”) is allowed to certain favoured customers of the business. These customers are listed in the Preferred Customer Table (see Data_Table worksheet). The indexing in this table is the concatenation of three values: customer name, the underscore character, and the customer suburb (e.g., Smith_Aspley). You must retrieve the appropriate preferred customer discount rate from this table. You must also recognise that only a small percentage of customers are given “preferred” status. This means that a lookup of the table may not always return a positive result. You will develop a formula combining VLOOKUP, CONCATENATE, and IFERROR. You must use the appropriate named range within your VLOOKUP function. The resulting data should be formatted as Percentage with zero decimal places."
The final solution(utilizing the excel function mentioned above) will be on the purchase table under "preferred purchaser discount %." These two tables are in separate sheets. Data table is sheet 1 and purchaser table is sheet 2 on excel 2013. If anyone can help me find a solution on how to complete this particular question I will be very grateful. Thank you in advance.
Below is the tables that are relevant to this question.
Data table
| Preferred Customer Table | |
| Customer Name | Customer Discount |
| Smith_Aspley | 0.1 |
| Monroe_Deagon | 0.06 |
| Dorney_Boondall | 0.08 |
<colgroup><col span="2"></colgroup><tbody>
</tbody>
| Purchaser Name | Suburb | Purchase Date | Product Ordered | Volume Ordered (kg) | Total Order Cost/Kg | Preferred Purchaser Discount % |
| Smith | Aspley | 42563 | Apples | 10 | $2.15 | |
| Butler | Sandgate | 42566 | Pears | 20 | $1.65 | |
| Dorney | Boondall | 42570 | Bananas | 30 | $1.65 | |
| Thomas | Sunnybank | 42570 | Pineapples | 40 | $1.62 | |
| Higgins | Enoggera | 42571 | Apples | 10 | $2.15 | |
| Simmons | Tarragindi | 42571 | Pears | 20 | $1.65 | |
| Monroe | Deagon | 42572 | Bananas | 30 | $1.65 | |
| Moore | Mt Cootha | 42573 | Pineapples | 40 | $1.62 | |
| White | Indooroopilly | 42574 | Apples | 10 | $2.15 | |
| Thompson | Toowong | 42576 | Pears | 20 | $1.65 | |
| Jonas | Logan City | 42578 | Bananas | 30 | $1.65 | |
| Drysdale | Sandgate | 42581 | Pineapples | 40 | $1.62 | |
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
