1 Reply Latest reply: May 8, 2013 7:09 PM by Wayne Contello
j.n.j Level 1 Level 1 (0 points)

how can i use a table for a referance to another eg when i type a word in a cell, i will like it to match the word with another table then return the information in the cell i am using

iWork's, OS X Mountain Lion (10.8)
  • Wayne Contello Level 6 Level 6 (16,185 points)

    you can use vlookup() (or any of the lookup family of functions) to locate an item based on a key value:


    Here is an example of something you can do with two tables:


    Screen Shot 2013-05-08 at 9.06.40 PM.png


    The table on the right is title "Data" and stores a list of names with age and favorite color.


    The table on the left uses the value in the first column to lookup up information in the table Data


    in the table on the left:

    B2=IFERROR(A2&" is " & VLOOKUP(A2, Data :: A:D, 2, 0)&" years old and likes the color "& VLOOKUP(A2, Data :: A:D, 3, 0), "NOT FOUND")


    I know this look complicated.  so I'll break it up into smalled pieces:

    Screen Shot 2013-05-08 at 9.06.47 PM.png

    first the "&" is called the concatenate operator and joins two strings.  like this:

    a string is a set of characters between double quotes.


    so "string 1" & "string 2" becomes "string 1string2"  or "Sam " & "Jones" becomes "Sam Jones"


    you can use cell references instead of strings directly in which case the concatenation is performed on the contents of the cells.


    so if cell A1 contains "Hi " and the cell A2 contains "There"  then A1 & A2 will result in "Hi There"


    so you could add the formula

    A3=A1 & A2


    this is short hand for select cell A3 then type everything including the A3 so that A3 contains "=A1 & A2" (omit the double quote)


    OK.  So the formula I provided concatenates several items together:


    it concatenates A2, then the string " is " then a formula, then the string " years old and likes the color " then a formula


    the two formulas (highlighted in blue) perform a lookup of the value in cell A2 in columns A thru D of the table named "Data".  If if finds the value in cell A2 in the first column of the lookup range in the table Data (column A) then it returns the value from the same row but in the second or third column.


    all that is in a function calld iserror() to trap the condition where the calue you enter in A2 does not exist in the table Data:


    Screen Shot 2013-05-08 at 9.07.24 PM.png



    You will find the Numbers users guide and function reference helpful.  You can download then from Apple here: