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?

Report •

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 •

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 Slow
2 Acworth Jane Same
3 Athens

Can I make it work foa a column, not a cell?

Report •

July 2, 2009 at 11:35:02
name city county
Joe Acworth
Jane Acworth
Steve Athens
Bob Athens
May Blakely

Hopefully the spacing stays

Report •

Related Solutions

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 •

Ask Question