On the Cities worksheet, click cell F13. The lodging is based on a multiplier by City Type. Some cities are more expensive than others. Insert a VLOOKUP function that looks up the City Type (B13), compares it to the City/COL range (A7:B10), and returns the COL percentage. Then multiply the result of the lookup function by the Total Base Lodging (B5) to get the estimated lodging for the first city. Copy the function from cell F13 and use the Paste Formulas option to copy the function to the range F14:F18 without removing the border in cell F18.

File Home Insert Draw Page Layout Formulas Data Review view 9 Tell me what you want to do Trace Precedents Show Formulas Define Name E Calculate Now Trace Dependents Error checking Use in Formula Insert AutoSum Recently Financial Logical Text Date & Lookup & Math & More Name Calculation Calculate Sheet Function used Time Reference Trig Functions Manager d Create from Selection i Remove Arrows B Evaluate Formula window options Function Library Defined Names Formula Auditing Calculation F13 8/18/2018 1 Departure Date Ground Transportation Summary Trip Costs 2 Return Date 8/23/2018 Round-trip Shuttle Average Rental Car per Day 30 Lowest 4 Lodging Base/Night 115 Rental Car Total 150 Total Base Lodging 575 COL 95% 110% 125% 140% Rental Shuttle or Total 12 Destination City City Type Car? Airfare Rental Lodging Meals Cost 367 50.00 13 Boston 375 No 14 Miami 392 150.00 Yes 330 5 Orlando Yes 299 150.000 285 16 Philadelphia 479 50.00 375 No 17 San Antonio 218 150.00 Yes 285 18 Washington DC No 469 50.00 420 20 DC Places Cities A share