Daniel has attended:
Excel Advanced course
Inserting lists that correlate to different figures to fit into
Hello,
I am making a relatively simple formula, however, I cannot remember how to add a list. So for example, I have:
fuel consumption per hour 10 gallons
number of hours cruising 204
total number of gallons used 2040 gallons
type of fuel used: xyz
I want to add a list where I have written xyz with the following figures:
one gallon of fuel lbs of co2 per gallon
marine diesel 21.24
marine unleaded 93 19.88
marine unleaded 91 19.51
marine unleaded 89 19.52
Jet A 21.1
Biodiesel 5.02
And when you select for example marine diesel the number is 21.24 and that will multiply by 2040 and if you choose Jet A it will choose 21.10 and multiply 2040 by 21.1?
Would you please be able to assist on this?
Thank you very much,
Dan
RE: Inserting lists that correlate to different figures to fit i
Hi Dan
Thank you for using the forum to ask a question.
You would use a drop down list for the fuel type.
On the Data Tab, select Data Validation and choose List
Type in the list or highlight the column of Fuel Types
Once you have the fuel type, you would use a Vlookup, to lookup the second column which is the price.
Both of these topics are in the Excel Advanced Manual you received post course.
I'll attach an example to help.
Let me know if you require further assistance
Kind regards
Richard Bailey
Microsoft Certified Trainer
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Attached files...
RE: Inserting lists that correlate to different figures to fit i
Thank you very much!
I cant seem to get the value to match the list.
I have created the drop down list. Do I have to write the VLOOKUP formula in that same cell as the list?
And whenever I write the formula, it just comes out at VALUE?
one gallon of fuel lbs of co2 per gallon
marine diesel 21.24
marine unleaded 93 19.88
marine unleaded 91 19.51
marine unleaded 89 19.52
Jet A 21.1
Biodiesel 5.02
the formula i'm using is =VLOOKUP(Table1[one gallon of fuel],Table1[lbs of co2 per gallon],2,false)
But it doesn't seem to work. Can you advise why it is not functioning properly?
RE: Inserting lists that correlate to different figures to fit i
Hi Daniel,
The Vlookup needs the lookup value to be the cell where you have the drop down list. Then the Vlookup will search trough the table array to fin the fuel type.
In your Vlookup the Lookup Value is a whole column (Table1[one gallon of fuel]). Please change this to the cell where you have the drop down list.
Your Table Array is Table1[lbs of co2 per gallon], which are only one column in your table. Change Table1[lbs of co2 per gallon] to Table1.
Kind regards
Jens Bonde
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector