How to locate a cell and return value of neighbour cell

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.

Formula in Cell B2 of the query table is:

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

Regards,

Ian.

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

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

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

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:

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

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:

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