Excel question
Thread Starter
Join Date: Jan 2006
Location: Moree, NSW, Australia
Posts: 100
Likes: 0
Received 0 Likes
on
0 Posts
Excel question
Hi everyone,
I am trying to create a lookup or index but need some help. I have a spreadsheet for entering fuel burn figures which needs the aircraft rego, the operation the aircraft was involved in at the time of the fuel burn recording, and what the ops manual says should be the fuel burn.
So I created a table as shown:
I made 2 lists, one is called 'aircraft' and is the left column, the other is called 'operation' and is the first row. The numbers are the ops manual fuel burn figures (which are not correct btw)
Now here is one line from my spreadsheet:
The 'AVM' cell is from the list as is the 'spraying' cell. Each of these cells has a dropdown box which refers to the above mentioned lists. What I want to do is fill the cell called 'fuel flow guide FCOM' from the table, in other words that cell needs to cross reference my table according to the rego and the operation.
I have tried the web and keep trying different things such as lookup, index, vlookup etc, but I am doing something wrong!!!Can anyone help me?
I am trying to create a lookup or index but need some help. I have a spreadsheet for entering fuel burn figures which needs the aircraft rego, the operation the aircraft was involved in at the time of the fuel burn recording, and what the ops manual says should be the fuel burn.
So I created a table as shown:
I made 2 lists, one is called 'aircraft' and is the left column, the other is called 'operation' and is the first row. The numbers are the ops manual fuel burn figures (which are not correct btw)
Now here is one line from my spreadsheet:
The 'AVM' cell is from the list as is the 'spraying' cell. Each of these cells has a dropdown box which refers to the above mentioned lists. What I want to do is fill the cell called 'fuel flow guide FCOM' from the table, in other words that cell needs to cross reference my table according to the rego and the operation.
I have tried the web and keep trying different things such as lookup, index, vlookup etc, but I am doing something wrong!!!Can anyone help me?
Last edited by Warbo; 23rd Sep 2012 at 00:28.
Join Date: Aug 2007
Location: USA
Posts: 2
Likes: 0
Received 0 Likes
on
0 Posts
You could use a combination of VLOOKUP and MATCH. Something like:
=VLOOKUP(A8,$A$1:$E$4,MATCH(B8,$A$1:$E$1,0),FALSE)
where A8 holds the value of the row you're interested in (aircraft reg in your case), B8 holds the column you're interested in (operation in your case), $A$1:$E$4 is the entire table and $A$1:$E$1 is the row of column headings. The idea is that MATCH is used to select the column for VLOOKUP.
Hope this helps
=VLOOKUP(A8,$A$1:$E$4,MATCH(B8,$A$1:$E$1,0),FALSE)
where A8 holds the value of the row you're interested in (aircraft reg in your case), B8 holds the column you're interested in (operation in your case), $A$1:$E$4 is the entire table and $A$1:$E$1 is the row of column headings. The idea is that MATCH is used to select the column for VLOOKUP.
Hope this helps
