iWork Numbers: Finding a string value based on a string

Discussion in 'Mac Apps and Mac App Store' started by ert3, Jul 18, 2010.

  1. ert3 macrumors 6502a

    ert3

    Joined:
    Dec 10, 2007
    #1
    I've been trying to tweak the budget template in Numbers for iPad so I can have a separate sheet which collects data based on a string value (categories) and returns another string (details) and the number value from the corresponding number values

    I tried using the basic if statement but I don't know how to get it to compare strings rather than looking for a Boolean.

    ----EDIT------

    I am going to have a whole bunch of transactions in the transaction table.

    What I want to do is sort those transactions so when a transaction falls in the category of say EXTRA i want everything labeled EXTRA to be copied into its own table.
     
  2. deadwulfe macrumors 6502a

    deadwulfe

    Joined:
    Feb 18, 2010
    #2
    Not exactly sure what you're trying to accomplish, but VLOOKUP or HLOOKUP might be of use to you.
     
  3. ert3 thread starter macrumors 6502a

    ert3

    Joined:
    Dec 10, 2007
    #3
    V/H LOOKUP expect numbers instead of strings

    what im trying to accomplish is the following as best as i can describe it.

    I am going to have a whole bunch of transactions in the transaction table.

    What I want to do is sort those transactions so when a transaction falls in the category of say EXTRA i want everything labeled EXTRA to be copied into its own table.
     
  4. ert3 thread starter macrumors 6502a

    ert3

    Joined:
    Dec 10, 2007
    #4
    This kinda works but is not the ideal fix i wanted as this just compares the length of two strings

    =IF(DELTA(LEN(Priorities :: $A$5),LEN(Transactions :: C2)),Transactions :: B2,"N/A")

    What this does is return the description of a transaction (Transactions B2) based on the length of the name (Transactions C2) and the base name (Priorities A5)

    this only works because each of my categories's names is diferent in length
     
  5. deadwulfe macrumors 6502a

    deadwulfe

    Joined:
    Feb 18, 2010
    #5
    I'm more familiar with Excel's formulas than Numbers and Excel V/HLOOKUP doesn't expect any particular type of data, it just matches what it looks up, be it string or number.

    I'm also not on my Mac to see if it works the same way or if it does, indeed, only lookup numbers.
     
  6. deadwulfe macrumors 6502a

    deadwulfe

    Joined:
    Feb 18, 2010
    #6
    That is something I don't have any idea how to do, even in Excel without learning VBA.
     
  7. Panch0 macrumors 6502a

    Joined:
    Feb 23, 2010
    Location:
    Virginia
    #7
    I think this is sort of what you want - VLOOKUP

    I tried this using the check register tmeplate rather than the budget template as it already has a transactions table with sample data. I added a new table and entered the following formula:

    =VLOOKUP("Gas",Transactions :: D3:F14,2,FALSE)

    VLookup can search for text, but it must be in the first (leftmost) column of the range. Also, you can only get data included in the range, so if there were columns to the left of the search term, they won't be available - you could overcome this by adding an extra column to the table that duplicates the category, or just by moving the category column to the left.

    check the help for the parameters of VLOOKUP...
     

Share This Page