7 Replies Latest reply: Nov 21, 2012 9:38 PM by Barry
Garden Frugal Level 1 Level 1 (0 points)

Hey all,  I'm new to using numbers (any spreadsheet really).  I can't figure out how to make this work.

 

Pic1.jpg

What I want to do for example is lookup a weight value in (F) by matching all the criteria in (A),(B),(C),(D) & (E).  If possible I would like my search criteria to match everything exactly except the Temp in (E).  The search temp can be warmer or greater than the temp listed. 

 

I've tried using Index and Match together but I can't figure out how to match more than one valvue.  If all the other criteria is matched there should only be one weight to return.  I can match one criteria valvue but it only returns the first or last match.  I need it to match each item and return one weight.  What am I doing wrong??

 

Please help, and keep it simple for the stupid me.

 

Thanks

  • 1. Re: How to use Index, Match, or lookup for multi cell data..
    Wayne Contello Level 6 Level 6 (13,615 points)

    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?

  • 2. Re: How to use Index, Match, or lookup for multi cell data..
    Garden Frugal Level 1 Level 1 (0 points)

    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))

  • 3. Re: How to use Index, Match, or lookup for multi cell data..
    Garden Frugal Level 1 Level 1 (0 points)

    I keep getting MATCH couldn't find the value ""

  • 4. Re: How to use Index, Match, or lookup for multi cell data..
    Barry Level 7 Level 7 (29,180 points)

    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.

    Picture 13.png

    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.

     

    Formula:

     

    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.

     

    Regards,

    Barry

  • 5. Re: How to use Index, Match, or lookup for multi cell data..
    Garden Frugal Level 1 Level 1 (0 points)

    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. 

    Pic2.png

    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.

     

    Clint

  • 6. Re: How to use Index, Match, or lookup for multi cell data..
    Garden Frugal Level 1 Level 1 (0 points)

    I did it all over like you did above.  Thanks Barry.  That was a lot more logical format.  Working great.  Thanks.

  • 7. Re: How to use Index, Match, or lookup for multi cell data..
    Barry Level 7 Level 7 (29,180 points)

    Hi Cliint,

     

    Good to hear you were able to follow through and come to a solution. Happy to have helped.

     

    Regards,

    Barry