Skip navigation

HT3354: Numbers '09: Formula Tools

Learn about Numbers '09: Formula Tools

HT3354 how can i use one table for reference to another

218 Views 1 Reply Latest reply: May 8, 2013 7:09 PM by Wayne Contello RSS
j.n.j Calculating status...
Currently Being Moderated
May 8, 2013 5:02 PM

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 (12,625 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:

    http://support.apple.com/manuals/#productivitysoftware

Actions

More Like This

  • Retrieving data ...

Bookmarked By (0)

Legend

  • This solved my question - 10 points
  • This helped me - 5 points
This site contains user submitted content, comments and opinions and is for informational purposes only. Apple disclaims any and all liability for the acts, omissions and conduct of any third parties in connection with or related to your use of the site. All postings and use of the content on this site are subject to the Apple Support Communities Terms of Use.