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>

 

[Charleton]

Hi Steve,

I have this working splendidly.

=VLOOKUP(F2,A4:I26,MATCH(E2,B3:I3,0),FALSE)+IF(G2="YES",HLOOKUP(E2,B3:I27,25,FALSE),0)

Cheers!

 

[Charleton]

Wait, did I get something backwards? I think maybe so.

Also, why not build a relational database? Most of these values seem very repetitive. This might be a good visual chart, but there is a lot less going on than it seems.
Also, I added data validation on the input values. My formula did not account for the possiblity of a typo or a unlisted value.

 

[Peter_SSs]

Try this. If it does not cover all circumstances, give the circumstances where it fails, what the correct result should be for those circumstances, and why.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Max Altitude				ISA Dev	Req Alt	Anti-ice	Weight
2	WEIGHT	ISA DEVIATION (C)			-5	36000	NO	51000
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

 

[Charleton]

Ok, I got it in two formulas, but it works REALLY well (assuming your example should be 41000, not 51000 - which is not even on your table):

Paste this into Cell I1 (make this the color of the background)
=MID(CELL("address",INDEX(B3:I3,MATCH(E2,B3:I3,0))),2,1)&MATCH(F2,A4:A26,0)+3

This returns the value D10
(That is the cell where this max alt meets this ISA DEV)

Then, in Cell H2

=INDIRECT(I1)+IF(G2="YES",HLOOKUP(E2,B3:I27,25,FALSE),0)

You can make absolute and add error traps if you need.

Which a table though, data validation is still my advice.

With data validation, you can lock the cells and not have anyone do anything but choose the drop down values for the three inputs.


Hope this helps!

 

[Marcelo Branco]

@Peter

With low Required Altitudes, like F2 = 32000, your formula returns Unable
Maybe this fix it
=IFERROR(INDEX(A5:A27,AGGREGATE(14,6,(ROW(A5:A27)-ROW(A5)+1)/((B5:I27>=E2-(B4:I4=D2)*(B28:I28)*(F2="YES"))*(B4:I4=D2)),1)),"Unable")

M.

 

[Peter_SSs]

@Marcelo
You may be right but I'm not sure. Suppose the ISA Dev =-5, Anti-Ice = No and Req Alt = 32,000 then following the OP's method ..

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.

.. the process would fail at step 2 since there is no 32000 values in that column. So, it may be that such a requested altitude is not allowed/possible for that situation??


@Charleton
I think you are still working the wrong way around. I believe the OP is trying to find out the highest weight the aircraft can carry, not the altitude. You will see that 51000 is in the table - at cell A25 in my screen shot.

 

[Charleton]

Hi Peter, thanks for clarifying. I was totally confused by the way the rows and column headers are ordered, and how the Range from B4:B26 would have no specific answer based on the data provided. I apologize for any confusion I caused.

 

[Tetra201]

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

 

[Marcelo Branco]

@Marcelo
You may be right but I'm not sure. Suppose the ISA Dev =-5, Anti-Ice = No and Req Alt = 32,000 then following the OP's method .... the process would fail at step 2 since there is no 32000 values in that column. So, it may be that such a requested altitude is not allowed/possible for that situation??

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.