Numbers: cell references in cell references

[PCheese]

I'd like to referencing a cell in another table based on other cell values.

I have three tables: "working", "data A", and "data B". The "working" table has two columns where I enter data manually. For instance, the "working" table might have (with | as a cell delimiter):

name | level
======
data A | 3
data B | 2
data A | 4
data A | 5

and the data tables might look like:

level | value
======
1 | 62
2 | 95
3 | 142

I would like to add a third column to the right which contains the value from the corresponding table + row. For instance, the first row would contain the third cell of the "data A" table, the second would be the second cell of the "data B" table, etc.

How can I include cell references in a cell reference? Or am I approaching this problem the wrong way?

 

[mysterytramp]

I'm sure you're doing something simple, but I'm having a hard time visualizing how you want to refer to the separate tables.

I'd strongly recommend you take a look at the Numbers User's Guide. Chapter 6 talks about formulas and cell references. I bet five minutes and you're on your way.

mt

 

[PCheese]

Thanks, mysterytramp. I'll keep looking through the guide.

So far, I found a partial workaround using VLOOKUP.

However, now I have a bunch of ugly nested IF statements. Ideally I'd be able to refer to the table name via a cell value instead of this mess.

Anyone have a better way of doing this?

=
IF($A16="data A",
VLOOKUP($B16,data A :: $A$2:$D$31,2),
IF($A16="data B",
VLOOKUP($B16,data B :: $A$2:$D$31,2),
0))

 

[mysterytramp]

Check out the INDIRECT() function.

Your VLOOKUP can build the range string by pulling "data A" or "data B" (from column A, let's say. Then add the appropriate range identifier (let's say "$A$1:$B$1") then wrap it in the INDIRECT function.

It'll look something like this:

=VLOOKUP(C3,INDIRECT(A3&"::$A$1:$B$10"),2)

mt