Need help with Excel

Discussion in 'Mac Apps and Mac App Store' started by jchase2057, Mar 1, 2013.

  1. jchase2057 macrumors regular

    Joined:
    Dec 6, 2010
    Location:
    Detroit
    #1
    I am working on a spreadsheet for work. We currently have all of our rate cards for our different vendors organized by "Rate ID". Each of the rate ids starts with an abreviation for a city. NYC for New York, SAT for San Antonio, etc. Different regions are managed by different people. A region contains many cities. What I am looking to do is add a new column for regions. There are hundreds of different cities though. I need a formula that will allow to do this. I need something like, if the first 3 in the rate id column are NYC then northeast in region column.
     
  2. exegete77 macrumors 6502a

    Joined:
    Feb 12, 2008
    #2
    If you have hundreds of cities, then it might not be practical for a “simple” formula. The best approach is to set up a table on another worksheet within this work book, such that you have cities(three letter code) listed in one column and adjacent to it, list the region. It will help to name the table and the two columns. Then you can use a simple lookup formula and quick copy down the entire column.
     
  3. Pharmscott macrumors 6502a

    Pharmscott

    Joined:
    Dec 13, 2011
    Location:
    Sacramento, CA
    #3
    I agree with exegete77. The function being referred to is called VLookup. There are a few tricks to getting it to do what you want (sort the lookup column first is one requirement). But it's a powerful tool. Check Microsoft's help site if you've never used it before because most people wont figure VLookup out on their own (I didn't!).
     
  4. madrich macrumors regular

    madrich

    Joined:
    Feb 19, 2012
    Location:
    World Class City of Chicago
    #4
    BTW: VLookup has range lookup for an absolute exact match (false) or close or omitted match (true).

    Range_lookup. This argument requires either a true or false value, or it should be left blank.

    :D
     
  5. jchase2057 thread starter macrumors regular

    Joined:
    Dec 6, 2010
    Location:
    Detroit
    #5
    Thanks for the info. Will this allow me to have a column that I can filter by region? So say I select northeast I will be able to get all of the current rates for the northeast. Then as we get updated rate cards I can just copy them into the proper columns. I've read some vlookup tutorials and I am still a little unsure on how to make it work for my particular need.
     
  6. James Craner macrumors 68000

    James Craner

    Joined:
    Sep 13, 2002
    Location:
    Bristol, UK
    #6
    Hi I have put together a quick and dirty video tutorial for you. Hope it helps

     
  7. jchase2057 thread starter macrumors regular

    Joined:
    Dec 6, 2010
    Location:
    Detroit
    #7
    Thank you so much. One of the kindest acts I've seen on these forums.
     
  8. James Craner macrumors 68000

    James Craner

    Joined:
    Sep 13, 2002
    Location:
    Bristol, UK
    #8
    Your welcome - I hope you found it useful.
     

Share This Page