Skip navigation

How to locate a cell and return value of neighbour cell

526 Views 8 Replies Latest reply: May 14, 2013 11:11 PM by Barry RSS
papalapapp Level 1 Level 1 (80 points)
Currently Being Moderated
May 14, 2013 3:13 AM

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 4 Level 4 (3,910 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.

  • jaxjason Level 4 Level 4 (3,320 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 4 Level 4 (3,910 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

  • Barry Level 7 Level 7 (29,095 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,095 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,095 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

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

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.