Currently Being ModeratedNov 19, 2012 3:54 PM (in response to Garden Frugal)
The eaiset thing to do is filter this table using the reorganize panel. To do this select the table, then select the menu item "Table > Show Reorganize Panel" (the last item).
If this does not work for you, can you show your input (used for the look up) so we have an idea what you want to enter inorder to locate the output?
Currently Being ModeratedNov 19, 2012 9:21 PM (in response to Wayne Contello)
No that doesn't work. I need it to return the weight so I can use it for other calculations. I need it to match all the other criteria. This is only a small sample of this table. My sample above shows the same info in those columns but they really have many other conditions. So for example
In another table I enter the current conditions. Let's say the current conditions are HIO,DRY,ALT T/O-1,13,10. I need it to match each of those and return the weight of 49474 so I can use that answer in another calculation.
So my question is how do I write a formula to match multi criteria and return one answer? Does it have to do with an array? I don't know how to do an array.
I thought something like:
=INDEX(Analysis :: F2:F61,MATCH('ALT T/O-1' :: A2&'ALT T/O-1' :: B2&'ALT T/O-1' :: C2&'ALT T/O-1' :: D2&'ALT T/O-1' :: E2,Analysis :: A2:A61&Analysis :: B2:B61&Analysis :: C2:C61&Analysis :: D2:D61&Analysis :: E2:E61,0))
Currently Being ModeratedNov 19, 2012 10:00 PM (in response to Garden Frugal)
I keep getting MATCH couldn't find the value ""
Currently Being ModeratedNov 19, 2012 10:19 PM (in response to Garden Frugal)
The difficulty here is that MATCH and VLOOKUP return the values from the single row with the first match best fitting the type of match specified. VLOOKUP searches from the bottom of the column, MATCH form the top.
I'd suggest a rearrangement of your data to allow a search with fewer steps.
Since you want an exact match for the first four columns and a 'smallest value equal to or greater than' match for the fifth column I would suggest concatenating the values in columns A through E into a single value in a new column F inserted after column E.
For the part of the data table shown, this concatenated value would remain the same for 13 rows, while the temperature ranges in five degree steps from -20 to +45, then a single three-degree step to 48.
One value in those concatenated then changes, and the new concatenated value repeats for the next 13 rows with the temperature passing through the same range with the same steps.
If this pattern is consistent (or even reasonably consistent) through the whole table. then a rearrangement of the data as shown below will greatly reduce table size and search time.
Each distinct set of values A though D is presented on only a single row, the four values are concatenated in column E, and are followed by the temperature values, one per column, in the header row, with the associated weights for those conditions and that temperature listed across that row.
Note: weights in row 2 (runway 13) are taken from your table. Boredom with retyping then set in, so those in row 2 (runway 31) are formula generated to present a series of decreasing weights, but bear no other relation to those in your table.
With the table presented in this form, the search is reduced to a single OFFSET expression, with the ROW offset determined by matching the concatenated value of columns A-D of the Query table with their exact counterpart in the Data table, and the column offset determined by matching the temperature in the Query with its closest less than or equal to match in row 1 of Data.
Query::F2: =OFFSET(Data :: $A$1,MATCH(A&B&C&D,Data :: $E,0)-1,MATCH(E,Data :: $1:$1,1)-1)
Not handled: What do you want returned for temperatures less than -20 or greater than +48 degrees? As written, the formula will return a not found error for -21 and below, and the value associated with 48° for 49 and above.
Currently Being ModeratedNov 20, 2012 8:40 AM (in response to Barry)
Wow! Thanks so much Barry.
That was a great reply. That would work great but I also have other data that I need it to return. I was just hung up on step one.
I don't want a return for above 48 or under -20, that exceeds limits.
I combined the rows into one and can now match current conditions to it. I can use a simple LOOKUP now.
I have many more calculations to do before I'm done. This is the goal is this. I have another table where I input the current conditions. For example lets say HIO,DRY,ALT,13 and Temp of 8. I need it to match those conditions and return a weight. The temp can be <= to the temp in the table but not >. Once I have that weight I have to apply some corrections. For example lets say there is a tail wind of 5. I need to multiply the TW # by 5 and subtract that from the weight returned. Then I need to make corrections for QNH. so example for multiply HI QNH by 2 and subtract from the weight returned. Once all the corrections are made I need to compare that weight to the weight listed for the same conditions in Climb. Then I use the lower of those two. Once that's all done, I have to make sure that calculated weight is >= what our current weight is. If our current weight is less than that calculated weight, then I need to match our current conditions to all the criteria except the temp. I just need to find a weight that is higher than our current weight and return the speeds column. I know that's a lot of gumbo. I'm just trying to explain what I have to do. I wish I could simplify the table like you did above, but I don't think it's possible with the other data and calculations I need to do. I think I have to keep it they way I have it. Don't you think?
Thanks so much for your help. That method you described above would work if I didn't have all this other data to use as well. I now combined all the data into one cell on the far right.
Now I can just use this =LOOKUP('ALT T/O-1' :: F2,Analysis :: P,Weight) to get the weight. Then I do the same thing to make corrections.
Is there a better way of doing this?
Thanks again for your help. You put way more work into it than I though anyone would. Very helpful.
Currently Being ModeratedNov 21, 2012 7:18 PM (in response to Garden Frugal)
I did it all over like you did above. Thanks Barry. That was a lot more logical format. Working great. Thanks.
Currently Being ModeratedNov 21, 2012 9:38 PM (in response to Garden Frugal)
Good to hear you were able to follow through and come to a solution. Happy to have helped.