how to solve the following in excel

[naderassaleh]

DEARS,
iam trying to write a formula to give me fuel figures' effect or index based on the the amount of fuel onboard and fuel density and aircraft type. i could write a formula but it give fuel index for one type of density

=VLOOKUP($C$7,CHOOSE(VLOOKUP($C$6,CHOOOSE,3,FALSE),FUEL.A319,FUEL.320,FUEL.A321,FUEL.340.AB,FUEL.330),2)

$c$7 = fuel figure
$c$6 = aircraft registration



Below is aircraft registration

aircraft regstration aircraft type aircraft type
JYAYK A321 1
JYAYJ A321 1
JYAYT A321 1
JYAYV A321 1
JYAIE A330 2
JYAIF A330 2
JYAIG A330 2
JYAIA A340 3
JYAIB A340 3
JYAIC A340 3


below table for A340 aircrafts fuel.340.ab table


fuel den .773 .779 .785 .797 .803 .809 .815
weight
500
-1 -2 -2 -2 -2 -2 -2
1000 -3 -2 -2 -2 -4 -4 -4
1500 -2 -3 -3 -3 -3 -3 -3
2000 0 0 0 0 0 0 0
2500 1 1 1 1 1 1 1
3000 1 1 1 1 1 1 1
3500 2 2 2 2 1 1 1

below fuel index for A330 aircrafts. FUEL.A330

fuel den. .775 .780 .785 .790 .795 .80 .805 .815
weight 3 3 3 3 3 3 3 3
2000 5 5 5 5 5 5 5 5
4000 8 8 8 7 7 7 6 6
6000 5 5 5 4 4 4 4 4
8000 1 1 1 1 1 1 1 1
10000 0 0 0 0 0 0 0 0
12000 -1 -1 -1 -1 -2 -2 -2 -2


below fuel index for A321 aircfafts FUEL.A321 TABLE


fuel den. .76 .77 .78 .785 .79 .80 .81 .82
weight
1000 -1 -1 -1 -1 -1 -1 -1 -1
1500 -1 -1 -1 -1 -1 -1 -1 -1
2000 -2 -2 -2 -2 -2 -2 -2 -2
2500 -4 -4 -4 -4 -4 -4 -4 -4
3000 -2 -2 -2 -2 -2 -2 -3 -3
3500 -1 0 0 0 0 0 -1 -1
4000 1 1 1 1 1 1 1 1


PLZZZZZZZZZ HELP ME.

 

[benmcclave]

Hello,

You could try this (untested) formula. I put the CHOOSE function at the front, then use INDEX/MATCH for the appropriate ranges to find the proper Density and Weight. Just replace the <WEIGHT>, <WEIGHT COLUMN>, <DENSITY>, and <DENSITY ROW> arguments with the appropriate ranges/values. Hope this helps:

=CHOOSE(VLOOKUP($C$6,CHOOOSE,3,FALSE),
INDEX(FUEL.A319,MATCH(<WEIGHT>, <WEIGHT COLUMN>, 0), MATCH(<DENSITY>, <DENSITY ROW>, 0)),
INDEX(FUEL.A320,MATCH(<WEIGHT>, <WEIGHT COLUMN>, 0), MATCH(<DENSITY>, <DENSITY ROW>, 0)),
INDEX(FUEL.A321,MATCH(<WEIGHT>, <WEIGHT COLUMN>, 0), MATCH(<DENSITY>, <DENSITY ROW>, 0)),
INDEX(FUEL.A340.AB,MATCH(<WEIGHT>, <WEIGHT COLUMN>, 0), MATCH(<DENSITY>, <DENSITY ROW>, 0)),
INDEX(FUEL.A330,MATCH(<WEIGHT>, <WEIGHT COLUMN>, 0), MATCH(<DENSITY>, <DENSITY ROW>, 0)))