# Excel WORD formula?

July 2, 2009 at 09:09:59
Specs: Windows 2003
 Is it possible to translate a column into another by giving it parameters? Ex.: we receive a bill with approx 1000 rows, but the bill lists clients by "city", but we pay different amounts per county. Can I enter city x=county y once and use that formula for each bill to track spending? Please help!

See More: Excel WORD formula?

#1
July 2, 2009 at 09:57:17
 You could use a VLOOKUP table to find the county that is associated with each city, but you would need to build that table first.For example, if you had a table like:``` A B 1 City1 Blue County 2 City2 Green County 3 City3 Blue County 4 City4 Red County```And you had City2 in F1, you could use this:=VLOOKUP(F1,\$A\$1:\$B\$4,2,0) which would return Green County.If you have 2 or more cities with the same name in different counties, you will need to force them to have unique names because VLOOKUP is only going to find the first one. There's a method to "automatically" create unique names when there are duplicates in a list, but I won't go into that unless you need it.

Report •

#2
July 2, 2009 at 10:32:07
 So I get the VLOOKUP, but I need it to be for the column: City Name **County**1 Acworth Joe Slow2 Acworth Jane Same3 Athens 45Can I make it work foa a column, not a cell?Thanks!

Report •

#3
July 2, 2009 at 11:35:02
 name city countyJoe Acworth Jane Acworth Steve Athens Bob Athens May Blakely Hopefully the spacing stays

Report •

Related Solutions

#4
July 2, 2009 at 13:21:05
 Use pre and /pre (enclosed in <>) to get text to stay where you want in this forum or click on the pre symbol above the Reply box and put your text in between. It'll take some playing around, but that's how I got the table in my post to line up. Done with pre:``` 1 2 3 4 5 6 7 A B C D E F G h i j k l m n ```If you drag my VLOOKUP formula down a column, (e.g. Column F, since I used F1 in my example), it will change to F2, then F3, then F4, etc. and lookup each value in the column.The \$A\$1:\$B\$4 (the lookup_array) will stay the same since the \$ makes it an Absolute Reference, not like the F1 which is a Relative Reference.Read up on VLOOKUP in Excel help to understand how each of the 4 arguments work.If you don't want to build the lookup_array for VLOOKUP, I guess you could use a Nested IF statement as long as you have less than 8 cities to check.

Report •