henrylambourne821
Level 1 (4 points)
iWork

Q: 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

by t quinn,Apple recommended
t quinn t quinn
Level 5 (5,063 points)
Mac OS X
A:

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.

Screen Shot 2016-08-24 at 8.31.41 AM.png

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

Posted on Aug 24, 2016 7:40 AM

Close
henrylambourne821

Q: Reference a whole table in numbers

  • All replies
  • Helpful answers

  • by t quinn,Apple recommended

    t quinn t quinn Aug 24, 2016 7:40 AM in response to henrylambourne821
    Level 5 (5,063 points)
    Mac OS X
    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.

    Screen Shot 2016-08-24 at 8.31.41 AM.png

    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

  • by SGIII,Apple recommended

    SGIII SGIII Aug 24, 2016 8:04 AM in response to henrylambourne821
    Level 6 (10,796 points)
    Mac OS X
    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