Reference a whole table in numbers

Question

Reference a whole table in numbers

I am currently using a VLOOKUP to find values in a large table. The amount of Rows will expand as I add more data but the columns will stay fixed.

VLOOKUP statements require to know where they are searching for content. So from Cells A2 to M:20 as an example.

VLOOKUP(SearchFor, SearchWhere, ReturnColumn, ExactMatch)

My current statement:

VLOOKUP(Sheet1::Table2::Name, Sheet1::Table1::A2:M20, 4, ExactMatch)

What I would love is just to search the whole table verse having to select an area, so that the table can grow and ever expand without needing to update the formula, much like when you select just a column or row. Is this possible?

Statement I would love:

VLOOKUP(Sheet1::Table2::Name, Sheet1::Table1::, 4, ExactMatch)

Hope this makes sense.

Thanks

H

MacBook Pro, OS X El Capitan (10.11.6)

Posted on Aug 24, 2016 7:22 AM

Reply

Answer 1

t quinn

Level 5

7,063 points

Aug 24, 2016 7:40 AM in response to henrylambourne821

Hi Henry,

I don't use the LOOKUP functions anymore as I find the INDEX/MATCH combo clearer and more versatile. It will do what you want.

As rows are added to the lookup table the formula will work for those added columns too.

VLOOKUP() should work this way also if you select the whole column rather than certain rows.

quinn

Reply

Answer 2

SGIII

Level 8

37,604 points

Aug 24, 2016 8:04 AM in response to henrylambourne821

henrylambourne821 wrote:

I am currently using a VLOOKUP to find values in a large table. The amount of Rows will expand as I add more data but the columns will stay fixed.

If your current formula

=VLOOKUP(Sheet1::Table2::Name, Sheet1::Table1::A2:M20, 4, ExactMatch)

is working as you want, but you simply want it to accommodate additional rows as you add them to the table, you could consider changing it to:

=VLOOKUP(Sheet1::Table2::Name, Sheet1::Table1::A:M, 4, ExactMatch)

The A:M references the entire table rather than a range within the table.

The approach using INDEX MATCH would be similar.

SG

Reply