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.
Got a tip for us?
Let us know
Become a MacRumors Supporter for $50/year with no ads, ability to filter front page stories, and private forums.
Need help with Excel
- Thread starter jchase2057
- Start date
- Sort by reaction score
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.
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!).
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.
