Question

On the Transactions worksheet, in cell H10, replace the static value by entering a VLOOKUP function to retrieve the Item's price from the Product_List table and then multiply it by the quantity to calculate the gross revenue. Use the fill handle to copy the formula down through H30.

On the Transactions worksheet, in cell J10, replace the static value by entering a HLOOKUP function to retrieve the discount value from the range B4:D6 based on the coupon code used in the transaction. Use the fill handle to copy the formula down through J30.

for 617 : * ДА 1 Product List В C D 2 Retail Price $ 49.95 $ 419.00 12.00 $ 49.00 $ 59.00 2.00 5.95 15.45 3 ItemID 4 X349R39 5 X487P48 6 T981Q11 7 C584123 8 C583K29 9 R483P24 10 F833K19 11 F375P67 12 C483T89 13 C492R32 14 C581149 15 C557P29 16 T822748 17 X733R14 18 X740029 19 F232P37 20 C884P23 21 R239557 Description Windbreaker (Resort) Windren Pro (right-handed) Resort golf balls Trimble Comforts Trimbel Striders Resort Tees Resort divot key chain Grabber golf gloves Resort cap Resort polo shirt golf shoe insert golf button, shirt golf button, shirt golf button, shirt Windren pro (left-handed) Resort golf bag (elite) Resort golf bag (carryall) Resort golf easy-go pull cart Category Clothing Clubs Accessories Shoes Shoes Accessories Accessories Accessories Clothing Clothing Accessories Clothing Clothing Clothing Clubs Accessories Accessories Accessories 14.95 39.95 11.39 32.95 32.95 32.95 453.00 129.00 57.00 97.00 $ $

Answer 1

1. H10 ( Gross Revenue): Vlookup Formuale to be used += Vlookup(Value,Range, Column Index number, True, False). VLookup is very interesting and useful formuale to see the items or mapping the required things from another sheet provided the format for the both the files , Value to be seen in both the files to be at first.

In this here In order to fill Gross Revenue: Product price list * Quantity to be done in single cell.

+( Vlookup( A10,Product List!A4:D21,4,0)*Transaction Analysis!D10)

Use fill handle to copy the formuale down to other rows.

2. J10 Coupon values need to be added Hlookup formulae to be used  += Hlookup(Value,Range, Column Index number, True, False). HLookup is very interesting and useful formuale to see the items or mapping the required things provided , Value to be seen in both the files to be at first

In this here, In order to fill Coupon values from Transactional Analysis

+Hlookup(E10,B4:D5,2,0)

Use fill handle to copy the formuale down to other rows.