## How to use Index, Match, or lookup for multi cell data..

403 Views 7 Replies Latest reply: Nov 21, 2012 9:38 PM by Barry
Calculating status...
Currently Being Moderated
Nov 19, 2012 3:25 PM

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

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

• Level 6 (11,905 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?

• Level 7 (28,815 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.

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

• Level 7 (28,815 points)

Hi Cliint,

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

Regards,

Barry

#### More Like This

• Retrieving data ...

#### Legend

• This solved my question - 10 points
• This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.