8 Replies Latest reply: May 14, 2013 11:11 PM by Barry
papalapapp Level 1 Level 1 (80 points)

Hi, I have a table containing data pairs in columns like [Title] [Value]. The table has multiple columns resulting in a table like this:

 

 

TitleValueTitleValueTitleValue
Apples100Bread120Pork90
Melons120Pancake150Beef160
Peaches110Pie55Fish100
...




 

 

In a different table, I want to enter a title and get the value next to it returned. E.g. I enter "Pancake" in column A and get "150" in column B.

 

I tried using Lookup but it doesn't support multi-column searches so I would have to create IF-chains for the pairs of columns.

 

The best way I could think of is to locate the address of the title cell and use Offset. Does anyone know a solution for this?


MacBook, OS X Mountain Lion (10.8), 2 GHz Core 2 Duo 1TB + 256 GB SSD
  • Yellowbox Level 5 Level 5 (7,285 points)

    Hi papalapapp,

     

    May I suggest you reorganise your database so that each row is a unique, single item. That makes it easy for VLOOKUP to search, and offers other advantages such as the ability to categorise foods or to sort the database.

     

    Screen Shot 2013-05-14 at 9.23.06 PM.png

     

    Formula in Cell B2 of the query table is:

     

    =VLOOKUP(A2,Table 1 :: B:C,2,1)

     

    Regards,

    Ian.

  • papalapapp Level 1 Level 1 (80 points)

    Ian, thanks for the suggestion. Unfortunately a re-organisation would be quite a challenge because there are a number of dependencies with other tables plus the matter of layout. So in this case I'd rather go with daisy-chaining VLOOKUPs.

  • jaxjason Level 4 Level 4 (3,535 points)

    I do analysis of users processes quite often in my day job and often times have to tell them the bad news that their workbooks are not formatted in a manner that will give them flexibility in the future for additional features, process steps, reporting, etc...

     

    This sounds like one of the cases where I would tell them it will be better in the long run to exert the effort now and go ahead and reformat into a properly formatted table to facilitate existing support and future expandability that also allows the existing functions to work in the manner they were designed.

     

    Short version: my recommendation if you worked with me  would be to follow Ians suggestion and reformat/rethink your formatting and data organization.

     

    Jason

  • Yellowbox Level 5 Level 5 (7,285 points)

    Numbers has a certain "charm" (reference Jerry, Jerrold Green) whereby there can be a front man on the "bridge" (Captain Kirk, in charge), a "chart room" (database, maintained by First Mate Mr Spock), and an "engine room" (where Engineer Mr Scott does the work).

     

    These functions can be separated and hidden in different Tables and Sheets in Numbers. Communication is by formulas.

     

    The database does not need to look pretty, but is must be easy to use. The Engine Room is below decks. All that matters is that Starship Enterprise performs its function. That is when the Charm of Numbers shows up in the final presentation.

     

    Ian.

     

    Message was edited by: Yellowbox. Waxing lyrical by me, not Jerry

  • papalapapp Level 1 Level 1 (80 points)

    I agree with both of you. Jason, "future expandability" is why I am here looking for a better solution than daisychaining columns. 

     

    But on the other side there is this thing called User which also has certain requirements. Hence the display in several columns instead of one long list. A single column seems be technically cleaner, but it would break the concept which is giving an overview and easy access to complex data. I need to find the best compromise.

  • Barry Level 7 Level 7 (29,215 points)

    You can have both.

     

    Put your desired layout up front, where you can see it.

     

    Use an auxiliary table, which can be on a separate sheet or otherwise hidden, to convert the display to a two column LOOKUP table on which VLOOKUP can do the job in the way it's designed to work.

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,215 points)

    Option 2:

     

    Although I don't really recommend it, You can use a daisy chain of VLOOKUP statements in a series of nested IFERROR  statements:

    Screen Shot 2013-05-14 at 10.05.09 PM.png

    Summary table (right),

    B2: =IFERROR(VLOOKUP(A,Table 1 :: A:B,2,0),IFERROR(VLOOKUP(A,Table 1 :: C:D,2,0),IFERROR(VLOOKUP(A,Table 1 :: E:F,2,0),"nf")))

     

    Fill down to end of table (not including the Footer Row.

     

    Footer row, column B: =SUM(B)

     

    Regards,

    Barry

  • Barry Level 7 Level 7 (29,215 points)

    More on "you can have both..."

     

    Here's an exmple that preserves your original table layout (in "Main"), uses it to generate an Auxiliary table ("Aux"), thenuses that table as a lookup table for VLOOKUP:

    Screen Shot 2013-05-14 at 10.57.49 PM.png

     

    Formulas:

     

    The complicated ones are in the Aux table:

    A2: =IF(ROW()-1<=COUNTA(Main :: A),OFFSET(Main :: $A$1,ROW()-1,0),IF(ROW()-1<=(COUNTA(Main :: A,Main :: C)),OFFSET(Main :: $C$1,ROW()-(1+COUNTA(Main :: A)),0),IF(ROW()-1<=(COUNTA(Main :: A,Main :: C,Main :: E)),OFFSET(Main :: $E$1,ROW()-(1+COUNTA(Main :: A,Main :: C)),0),"over")))

     

    Fill down to the end of column A.

     

    Separated for clarity:

     

    =IF(ROW()-1<=COUNTA(Main :: A),OFFSET(Main :: $A$1,ROW()-1,0),

      IF(ROW()-1<=(COUNTA(Main :: A,Main :: C)),OFFSET(Main :: $C$1,ROW()-(1+COUNTA(Main :: A)),0),

      IF(ROW()-1<=(COUNTA(Main :: A,Main :: C,Main :: E)),OFFSET(Main :: $E$1,ROW()-(1+COUNTA(Main :: A,Main :: C)),0),

      "over")))

     

    B2: =IF(ROW()-1<=COUNTA(Main :: A),OFFSET(Main :: $A$1,ROW()-1,1),IF(ROW()-1<=(COUNTA(Main :: A,Main :: C)),OFFSET(Main :: $C$1,ROW()-(1+COUNTA(Main :: A)),1),IF(ROW()-1<=(COUNTA(Main :: A,Main :: C,Main :: E)),OFFSET(Main :: $E$1,ROW()-(1+COUNTA(Main :: A,Main :: C)),1),"over")))

    Fill down to the end of column B.

     

    Note that this is the same formula as used in Column A, with only the three zeros in OFFSET changed to ones.

     

    Summary table:

     

    B2, and filled down to end of column B: =IFERROR(VLOOKUP(A,Aux :: A:B,2,0),"nf")

     

    Regards,

    Barry