- In the Rental Data worksheet, in column G, create a formula that uses a HLOOKUP function to assign a group code from the Instrument Groups range in the Rental information worksheet to the instrument listed in column B
Total Co 1 Renter 2 Calmos 3 Hanson 4 Charles 5 Roman 6 Nelson 7 Johnson 8 Ibe 9 Gilhooly 10 Jordan 11 Schmidtz 12 Dong 13 Alphetti 14 Wiley 15 McGraw 16 Ball 17 Borden Insur Shipping Group Insurance Shipping Instrument Rental Date Rental Perio Coverag Code Code Return Dat - Rental $ Cost Cost Flute 8/15/2018 3 Yes Pickup Clarinet 8/28/2018 3 Yes Pickup French Horn 7/13/2018 9 Yes Ground Piccolo 7/17/2018 3 Yes Ground Flute 7/2/2018 3 Yes Pickup Saxophone 8/31/2018 3 Yes Ground Trombone 8/30/2018 9 Yes Pickup Flute 8/22/2018 3 No Ground Flute 7/15/2018 3 No Ground Trombone 7/3/2018 9 Yes Pickup Bassoon 7/17/2018 9 Yes Pickup Clarinet 9/11/2018 3 No Rush Trombone 7/30/2018 3 Yes Pickup Accordian 9/30/2018 3 Yes Pickup Trombone 8/24/2018 3 Yes Pickup Flute 9/5/2018 3 Yes Pickup Instrument Flute Renter 18 Krank 19 Pinkerton 20 Reed 21 Turrell 22 Aserty 23 Shenal 24 Meglech 25 Metz 26 Storval 27 Smith 28 Mogan 29 Mermot 30 Hofer 31 Golly 32 Interim 33 Loflin 34 Wagner 35 Assam Clarinet Flute Clarinet Clarinet Saxophone Accordian Piccolo Flute Flute Saxophone Flute Oboe French Horn Flute Clarinet Clarinet Saxophone Rental Date Rental Period Insur Cov, Shipping - Group Return Date Rental Insurance Shipping Total Co 9/8/2018 3 No Pickup 8/2/2018 3 Yes Pickup 8/9/2018 3 Yes Rush 8/12/2018 3 Yes Ground 7/24/2018 3 Yes Pickup 8/1/2018 9 Yes Rush 9/5/2018 3 Yes Ground 8/16/2018 3 No Ground 8/15/2018 9 Yes Pickup 8/8/2018 3 Yes Pickup 9/30/2018 3 Yes Pickup 9/19/2018 9 Yes Ground 7/28/2018 9 Yes Pickup 7/17/2018 Yes Ground 8/30/2018 No Pickup 8/14/2018 9 Yes Pickup 7/6/2018 3 Yes Pickup 7/7/2018 3 No Pickup ww 36 Mattery 37 Bingner 38 Flora 39 Albred 40 Canner 41 Sofly Flute Saxophone Trombone Flute French Horn Accordian 9/14/2018 9/30/2018 7/12/2018 7/9/2018 9/29/2018 8/4/2018 w w w w w Yes Yes No Yes Yes Yes Pickup Pickup Rush Ground Pickup Pickup 9 E F G . M N O P K L Instrument Group Clarinet Instrument Flute Group Code A Piccolo piccolo Trombone Oboe B C Saxophone Accordian Bassoon French Hor D D E E A B C D 1 Instrument Rental Charges Rental for for 9- Monthly 2 Group 3-months months Insurance 3 A $ 47 $ 128 $ 3 4 B $ 57 $ 138 $ 4 5 $ 90 $ 216 $ 6 6 D $ 100 $ 286 $ 12 7 E $ 400 $ 650 $ 25 8 9 10 11 12 13 Total Co 1 Renter 2 Calmos 3 Hanson 4 Charles 5 Roman 6 Nelson 7 Johnson 8 Ibe 9 Gilhooly 10 Jordan 11 Schmidtz 12 Dong 13 Alphetti 14 Wiley 15 McGraw 16 Ball 17 Borden Insur Shipping Group Insurance Shipping Instrument Rental Date Rental Perio Coverag Code Code Return Dat - Rental $ Cost Cost Flute 8/15/2018 3 Yes Pickup Clarinet 8/28/2018 3 Yes Pickup French Horn 7/13/2018 9 Yes Ground Piccolo 7/17/2018 3 Yes Ground Flute 7/2/2018 3 Yes Pickup Saxophone 8/31/2018 3 Yes Ground Trombone 8/30/2018 9 Yes Pickup Flute 8/22/2018 3 No Ground Flute 7/15/2018 3 No Ground Trombone 7/3/2018 9 Yes Pickup Bassoon 7/17/2018 9 Yes Pickup Clarinet 9/11/2018 3 No Rush Trombone 7/30/2018 3 Yes Pickup Accordian 9/30/2018 3 Yes Pickup Trombone 8/24/2018 3 Yes Pickup Flute 9/5/2018 3 Yes Pickup Instrument Flute Renter 18 Krank 19 Pinkerton 20 Reed 21 Turrell 22 Aserty 23 Shenal 24 Meglech 25 Metz 26 Storval 27 Smith 28 Mogan 29 Mermot 30 Hofer 31 Golly 32 Interim 33 Loflin 34 Wagner 35 Assam Clarinet Flute Clarinet Clarinet Saxophone Accordian Piccolo Flute Flute Saxophone Flute Oboe French Horn Flute Clarinet Clarinet Saxophone Rental Date Rental Period Insur Cov, Shipping - Group Return Date Rental Insurance Shipping Total Co 9/8/2018 3 No Pickup 8/2/2018 3 Yes Pickup 8/9/2018 3 Yes Rush 8/12/2018 3 Yes Ground 7/24/2018 3 Yes Pickup 8/1/2018 9 Yes Rush 9/5/2018 3 Yes Ground 8/16/2018 3 No Ground 8/15/2018 9 Yes Pickup 8/8/2018 3 Yes Pickup 9/30/2018 3 Yes Pickup 9/19/2018 9 Yes Ground 7/28/2018 9 Yes Pickup 7/17/2018 Yes Ground 8/30/2018 No Pickup 8/14/2018 9 Yes Pickup 7/6/2018 3 Yes Pickup 7/7/2018 3 No Pickup ww 36 Mattery 37 Bingner 38 Flora 39 Albred 40 Canner 41 Sofly Flute Saxophone Trombone Flute French Horn Accordian 9/14/2018 9/30/2018 7/12/2018 7/9/2018 9/29/2018 8/4/2018 w w w w w Yes Yes No Yes Yes Yes Pickup Pickup Rush Ground Pickup Pickup 9 E F G . M N O P K L Instrument Group Clarinet Instrument Flute Group Code A Piccolo piccolo Trombone Oboe B C Saxophone Accordian Bassoon French Hor D D E E A B C D 1 Instrument Rental Charges Rental for for 9- Monthly 2 Group 3-months months Insurance 3 A $ 47 $ 128 $ 3 4 B $ 57 $ 138 $ 4 5 $ 90 $ 216 $ 6 6 D $ 100 $ 286 $ 12 7 E $ 400 $ 650 $ 25 8 9 10 11 12 13
