Lookup a value in a table

[steve1123]

Hello,

I have a predicament regarding a table that I'm trying to solve.

There is a table with Weights along the left side, ISA Temperature Deviation along the top, and Anti-Ice correction along the bottom.

For a given weight, ISA Dev, and whether or not the Anti-ice system is on, an airplane can reach a certain max altitude, which this table will show you. Using a combination of VLookup, HLookup, and Match functions, I have already put together a formula to compute the max altitude for a given weight, ISA deviation, and Anti-ice status.

The Anti-ice system takes its power from the engines, so if the Anti-ice system is on, the aircraft cannot fly as high, hence the negative corrections if the system is on.

Now, the problem. There are some situations where a pilot may want to know, for a given ISA dev and Anti-ice status, the highest weight the aircraft can be at in order to reach a specified altitude. If the aircraft is too heavy at a given time, as the fuel is burned, the aircraft may get down to the required weight, at which point the aircraft can climb to the requested altitude.

Essentially, the 4 variables are Weight, ISA Dev, Altitude, and whether or not the Anti-Ice system is on. I need a way to "read" the table when the known values are ISA Dev, Altitude, and Anti-ice status on/off. The flow of the formula should go something like this:

If the Anti-ice system is not on:
1) Based on user input, ISA Dev, go to the column for the current ISA Dev.
2) Based on user input, Desired Altitude, go down that column to find the bottom-most instance of that altitude.
3) Move left to find the weight. This will represent the heaviest the aircraft can be an still reach and maintain that altitude.

I have attached the chart. The cell containing Max Altitude is A1. I have the fields for user input at the top of the chart for simplicity. The formula should be in G2.

For example, if the Anti-ice system is not on:
ISA Dev: -5
Anti-ice on?: No
Desired Altitude: 36000ft
Result should be: 51000Kg

If the Anti-ice system is on, this requires some extra work:
1) Take the given requested altitude and add the correction value at the bottom of the chart. In the example, 36000 + 5000 = 41000Kg
2) Repeat steps 1-3 above, essentially replacing 36000, with 41000 (since you have to subtract the 5000ft correction from 41000ft, in order to maintain the requested 36000ft).

In the above example, if the Anti-ice system is on, the result should be 39000Kg.

The main thing to keep in mind is that the service ceiling is 41000ft, so no matter what, the assumed altitude cannot go past 41000. If a pilot is requesting an altitude that cannot be reached at any weight, the result should produce an "Unable" response.

Thanks in advance for any assistance. I know this one is rather complicated. It's been stumping me for a while.

Max Altitude				ISA Dev	Req Alt	Anti-ice on?	Weight
WEIGHT	ISA DEVIATION (C)			5	38000	NO	RESULT HERE
	-15	-10	-5	0	5	10	15	20
30000	29000	34000	41000	41000	41000	41000	41000	41000
31000	29000	34000	41000	41000	41000	41000	41000	41000
32000	29000	34000	41000	41000	41000	41000	41000	41000
33000	29000	34000	41000	41000	41000	41000	41000	41000
34000	29000	34000	41000	41000	41000	41000	41000	41000
35000	29000	34000	41000	41000	41000	41000	41000	40000
36000	29000	34000	41000	41000	41000	41000	41000	40000
37000	29000	34000	41000	41000	41000	41000	41000	39000
38000	29000	34000	41000	41000	41000	41000	40000	39000
39000	29000	34000	41000	41000	40000	40000	40000	38000
40000	29000	34000	40000	40000	40000	40000	39000	38000
41000	29000	34000	40000	40000	40000	40000	39000	37000
42000	29000	34000	39000	39000	39000	39000	38000	37000
43000	29000	34000	39000	39000	39000	39000	38000	37000
44000	29000	34000	38000	38000	38000	38000	38000	36000
45000	29000	34000	38000	38000	38000	38000	37000	36000
46000	29000	34000	37000	37000	37000	37000	37000	35000
47000	29000	34000	37000	37000	37000	37000	36000	34000
48000	29000	34000	37000	37000	37000	37000	36000	33000
49000	29000	34000	36000	36000	36000	36000	35000	33000
50000	29000	34000	36000	36000	36000	36000	35000	32000
51000	29000	34000	36000	35000	35000	35000	34000	31000
51800	29000	34000	35000	35000	35000	35000	34000	31000
Correction ENG & WNG ANTI-ICE ON	0	-3000	-5000	-5000	-5000	-5000	-8000	-10000

<tbody>
</tbody>

 

[Peter_SSs]

Here is my take on it:

=IFERROR(LOOKUP(2,1/(INDEX(B4:I27,0,MATCH(E2,B3:I3))=F2-HLOOKUP(E2,B3:I27,25,0)*(G2<>"NO")),A4:A26),"Unable")

and some (very limited) test results:

-5 / 36000 / No: my formula 51800; Peter's formula "Unable"
20 / 21000 / Yes: my formula 51800; Peter's formula 51000

Hi Tetra

I'm not sure where those blue results come from? For me your formula returns 51000 and so does mine (it is the exact example shown in post 4)

The red example is one similar to what Marcelo is raising. 20 / 21000 is not a value available in the table so I don't know if it is a combination that is allowed or not. Given that your example was for "Yes" and 20/31000 is in the table it may well be valid. However, rather than speculate on what seems reasonable to us & adjust the formula to accommodate, I think we need the OP to clarify that issue. Your red example, though, did highlight a typo in my formula - I was one row short in one of my ranges, so it should have been as shown below which results in both our formulas returning the same result for the red example.
=IFERROR(INDEX(A4:A26,AGGREGATE(14,6,(ROW(A4:A26)-ROW(A4)+1)/((B4:I26=F2-(B3:I3=E2)*(B27:I27)*(G2="YES"))*(B3:I3=E2)),1)),"Unable")

Yes, if we strictly follow this sentence the formula must return Unable. But this, IMHO, can cause some confusion.
I was thinking in real life: could not the pilot ask for a lower altitude than those on the table? After all, 32,000 feet seems a reasonable altitude for a modern aircraft.
Another advantage in using greater or equal is that requests for non-exact altitudes such as 35500 would be accepted and treated as the value immediately above (36000).

M.

Marcelo, your argument about altitudes below the bottom row of the table sound reasonable, but as I said above, it would be better for the OP to clarify than for us to guess.
Similarly with your 35500 example, I'm not sure if your suggestion is an advantage or a disadvantage. It could be quite reasonable, but it could also be that such an entry is not allowed (in some columns at least) and "Unable" might highlight the incorrect entry rather than return the result from a nearby row. I just don't know.

 

[Tetra201]

... I'm not sure where those blue results come from? For me your formula returns 51000 and so does mine (it is the exact example shown in post 4)...

It was a typo in my post -- those results were for -5 / 35000/ No.

Now that you have corrected the range in your formula, it works OK.
Yet, mine has fewer reference calls

 

[steve1123]

Wow! Thank-you very much for the fast and thorough answers!

I'm just inbetween flights and don't have my computer readily available at the moment, so I can't check the formulas just yet, but I will as soon as I can.

Hopefully I can clarify some of the issues in the meantime.

1) 32000ft is indeed fairly common for the modern jet. However, there are instances, usually either due to weather or traffic constraints, where the pilot or air traffic control may be looking for the aircraft to climb to a higher altitude.

2) For the example of +20 and a requested altitude of 21000ft with the anti-ice ON, the result should be 51800Kg.
After adding the anti-ice correction factor of 10000ft, the user (or in this case the formula) must use an "effective" altitude (for lack of a better word, the altitude that must be found in the table) of 31000ft. The highest allowable weight at this altitude is 51800ft. As you can see, high ISA Dev with AI ON can be very limiting when getting into the higher altitudes.

The above is a more extreme example, but nevertheless one that would have to compute properly.

Typically, when the anti-ice is required, the ISA will be lower. So a more common example would be:
ISA: -5
AI: ON
Requested alt: 35000ft
The expected result should be 41000Kg
The flow would be: 35000ft, plus the AI correction of 5000ft = 40000ft, this is the "effective" altitude that we must go looking for in the table, within the ISA -5 column, and the highest allowable weight at 40000ft is 41000Kg.

3) Altitudes not listed on the chart.
This chart is typically used for higher altitudes. So if a requested altitude is "below" the lowest value on the chart, the computed result should be 51800Kg, since the aircraft can reach those lower altitudes at the max gross weight. This should be fairly straight forward when the AI system is OFF. However, if the AI is ON, a similar flow should still apply, only now with the "effective" altitude.
For example:
ISA: 0
AI: ON
Requested alt: 32000ft.
Computed result should be 48000Kg
So, the initial requested alt of 32000ft is "below" the lowest value on the chart for an ISA Dev of 0. However, after adding the AI correction of 5000ft, we now have to look for an "effective" altitude of 37000ft, and the highest allowable weight at that altitude would be 48000Kg.

4) No need to worry about non-whole thousands of ft, as we typically fly only at whole thousands of ft. I'll be using either data validation or a drop-down list to restrict the input.

5) One other thing I should mention is that I cant use VBA, as I will ultimately be looking to convert this excel file to an iOS app. Not that this has been an issue with the proposed solutions. I'm looking for suggestions on how to convert this to an app as well, but I'm thinking I should probably start another thread for that. So far, Open as App looks like the best option I've found, though it may be cost-limiting.

I hope this helps to clarify a confusing table and situation. Thank-you once again for all of your help, patience, and continued support. I look forward to checking out the formulas hopefully later tonight!

 

[Marcelo Branco]

Hopefully I can clarify some of the issues in the meantime.

2) For the example of +20 and a requested altitude of 21000ft with the anti-ice ON, the result should be 51800Kg.

So, it seems to me logical that with Anti-Ice "NO", the formula should also return 51800, not Unable.
If I am right, it is necessary to change the comparison from equal to greater or equal (see post # 6).

M.

 

[steve1123]

Yes that sounds correct. If it's the formula is showing it could maintain the requested altitude at a higher weight, then the answer should default to a max of 51800Kg, as that is the maximum weight allowable as per aircraft structural limitations

I'm hoping this clarifies the issue in question? Or did I misunderstand?

 

[Peter_SSs]

I'm hoping this clarifies the issue in question?

It certainly clarifies some things for me. Based on the additional information, here are two updated options for you to consider.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Max Altitude				ISA Dev	Req Alt	Anti-ice	Weight	Weight
2	WEIGHT	ISA DEVIATION (C)			15	32000	yes	39000	39000
3		-15	-10	-5	0	5	10	15	20
4	30000	29000	34000	41000	41000	41000	41000	41000	41000
5	31000	29000	34000	41000	41000	41000	41000	41000	41000
6	32000	29000	34000	41000	41000	41000	41000	41000	41000
7	33000	29000	34000	41000	41000	41000	41000	41000	41000
8	34000	29000	34000	41000	41000	41000	41000	41000	41000
9	35000	29000	34000	41000	41000	41000	41000	41000	40000
10	36000	29000	34000	41000	41000	41000	41000	41000	40000
11	37000	29000	34000	41000	41000	41000	41000	41000	39000
12	38000	29000	34000	41000	41000	41000	41000	40000	39000
13	39000	29000	34000	41000	41000	40000	40000	40000	38000
14	40000	29000	34000	40000	40000	40000	40000	39000	38000
15	41000	29000	34000	40000	40000	40000	40000	39000	37000
16	42000	29000	34000	39000	39000	39000	39000	38000	37000
17	43000	29000	34000	39000	39000	39000	39000	38000	37000
18	44000	29000	34000	38000	38000	38000	38000	38000	36000
19	45000	29000	34000	38000	38000	38000	38000	37000	36000
20	46000	29000	34000	37000	37000	37000	37000	37000	35000
21	47000	29000	34000	37000	37000	37000	37000	36000	34000
22	48000	29000	34000	37000	37000	37000	37000	36000	33000
23	49000	29000	34000	36000	36000	36000	36000	35000	33000
24	50000	29000	34000	36000	36000	36000	36000	35000	32000
25	51000	29000	34000	36000	35000	35000	35000	34000	31000
26	51800	29000	34000	35000	35000	35000	35000	34000	31000
27	Correction	0	-3000	-5000	-5000	-5000	-5000	-8000	-10000
Flight Calc

 

[steve1123]

Spreadsheet Formulas

Cell Formula H2 =IFERROR(AGGREGATE(14,6,A4:A26/(INDEX(B4:I26,0,E2/5+4)>=F2-INDEX(B27:I27,E2/5+4)*(G2="yes")),1),"Unable") I2 =IFERROR(-LOOKUP(0,-A4:A26/(INDEX(B4:I26,0,MATCH(E2,B3:I3))>=F2-HLOOKUP(E2,B3:I27,25)*(G2<>"no"))),"Unable") <tbody> </tbody>

<tbody>
</tbody>

Excel tables to the web >> Excel Jeanie HTML 4

Based on my testing so far, both of these formulas work flawlessly! Thank-you very much for everyone's hard work and assistance on this one.

While they both work perfectly in Excel, I've run into a problem implementing them through Open As App. I think the software has a problem with how the formula works, in that it seems to rely on a DIV/0! calculation, and it simply takes the highest weight before a result of DIV/0! is returned. In any case, that's not a fault of the formula, but rather the software of Open As App not being able to compute it properly. I have emailed their technical support department to see if they have any recommendations.

Thanks again!

 

[Peter_SSs]

Based on my testing so far, both of these formulas work flawlessly! Thank-you very much for everyone's hard work and assistance on this one.

You are welcome. It was something of a team effort.