# Data validation to give text a value that's used in a formula elsewhere on the sheet

We have a group planning to charter a sailboat as a vacation for 10 days. I'm trying to show the total costs of the trip. It's a simple spreadsheet listing many semi-fixed costs like food, beer, fuel, insurance, mooring fees, etc.

I know that Boat A costs 1000, Boat B costs 1500 and Boat C costs 2000. I want the user to be able to select from a drop-down Boat A, B, or C and have it affect the bottom-line sum.

How can I do that?

Currently, using Data Validation all I know how to do is have the values (1000, 1500, 2000) in the drop-down list. I want an end value to change automatically based on what the user picked (Boat A, Boat B, Boat C).

Update: 5/18/16 - Still not working 100% I have 6 boats listed yet 2 of the 6 do not display the correct value that is in the table. The table data is E2:F7
Boat1 5999
Boat2 6599
Boat3 4999
Boat4 5099
Boat5 6999
Boat6 6499
Cell B5 is my drop down list containing Boat1 to Boat6
Cell C5 is my formula to determine the cost of the boat selected divided by 3.
Cell C5 has the following formula =(VLOOKUP(B5,\$E\$2:\$F\$7,2))/3
It displays the correct value for all Boats except Boat1 & Boat2. For those is calculating based on a boat price of 6999...which Boat1 & 2 are not those prices.
Help!..so close

Then use the formula `=VLOOKUP(<cell with the dropdown>,TheTable,2,FALSE)` to calculate the price for the selected boat, and use this in the further calculations.
• Strange, that should work. Maybe there is an odd blank in the text list. Also, add `,FALSE` as a fourth parameter in the `VLOOKUP()`; that tells Excel to check for exact matches (the default is to take the nearest match): `=VLOOKUP(<cell with the dropdown>,TheTable,2,FALSE)`. If you have a misspelling or extra blank, that will flush it out, like `Baot 1` or `Boat 1`. – Aganju May 18 '16 at 21:09