CODEWITHSUNDEEP
excel
Laura Wooding
Laura Wooding

Reputation: 11

How can I create a formula from drop down list

Hello I really need help getting the right formula I need.

I need to be able to have a drop down list in one cell and then a formula in the next cell that works out the value.

For example in one cell the drop down list would contain >15, 15-20,20-25,25-30 and so on. This shows how many miles an employee has travelled. I'd like the next cell to state a figure for each option of the drop down for example 15-20 miles = £4.75.

Any idea's on how to calculate this would be most helpful and I would be forever in your debt

Laura

Upvotes: 1

Views: 34771

Answers (1)

Phil Applegate
Phil Applegate

Reputation: 567

You will probably need to use a VLOOKUP formula.

Let cell A1 contains your drop down menu (created with Data->Validation->List then selecting a source which in this case will be B1:B3).

Let B1:B3 contain the miles, so 10,20,30

Let C1:C3 contain the costs, so £1.00, £2.00, £3.00

In D1 you can then type =VLOOKUP($A$1,$B$1:$C$3,2,FALSE). This should display in D1 the cost for the mileage selected in A1.

Hope this helps.

Upvotes: 2

Related Questions