ping: excel experts

cthomer5000 · 06-26-2006, 10:31 AM

How do i do the following in excel?

In column B I will be entereing 2 letter state codes (NY, NJ, etc). How do I get excel to take that data and translate it to a pre-definied value and place it in another column in that row? I have 50 states and 50 numeric values...

For example, if B2=NY, then G2=5

I really only know the basics with excel so i have no idea on where to start with this....

Huckleberry · 06-26-2006, 10:35 AM

Set up a table with the conversion on another sheet.

Then use vlookup

G2 = VLOOKUP(B2,table reference,2,FALSE)

The table will have two columns. First column will be the state codes and the second column will be the numeric code for each state.

QuikSand · 06-26-2006, 10:37 AM

I think a VLOOKUP function would do this efficiently.

Create a table, somewhere off to the side, with an alphabetized list of the state abbreviations, and the number you want to assign to that state. Follow the instructions for the VLOOKUP function, and this ought to work pretty easily. (You'll be putting the VLOOKUP function into cell G2 in your example above)

...er, what he said.

wade moore · 06-26-2006, 10:37 AM

If you need help with this, feel free to IM me. In fact, we could probably use Google Spreadhseets and I could show you.

Huckleberry · 06-26-2006, 10:38 AM

Sample spreadsheet

Now that wade moore mentions it, I have a Google Spreadsheets account I need to activate.

FrogMan · 06-26-2006, 10:45 AM

what they all said, VLOOKUP is your friend...

FM

cthomer5000 · 06-26-2006, 01:06 PM

thanks for help on this (wade especially).

06-26-2006, 10:35 AM	#2
Huckleberry College Starter Join Date: Dec 2001	Set up a table with the conversion on another sheet. Then use vlookup G2 = VLOOKUP(B2,table reference,2,FALSE) The table will have two columns. First column will be the state codes and the second column will be the numeric code for each state. __________________ The one thing all your failed relationships have in common is you. The Barking Carnival (Longhorn-centered sports blog) College Football Adjusted Stats and Ratings

06-26-2006, 10:37 AM	#3
QuikSand lolzcat Join Date: Oct 2000 Location: Annapolis, Md	I think a VLOOKUP function would do this efficiently. Create a table, somewhere off to the side, with an alphabetized list of the state abbreviations, and the number you want to assign to that state. Follow the instructions for the VLOOKUP function, and this ought to work pretty easily. (You'll be putting the VLOOKUP function into cell G2 in your example above) ...er, what he said. Last edited by QuikSand : 06-26-2006 at 10:37 AM.

06-26-2006, 10:38 AM	#5
Huckleberry College Starter Join Date: Dec 2001	Sample spreadsheet Now that wade moore mentions it, I have a Google Spreadsheets account I need to activate. __________________ The one thing all your failed relationships have in common is you. The Barking Carnival (Longhorn-centered sports blog) College Football Adjusted Stats and Ratings Last edited by Huckleberry : 06-26-2006 at 10:39 AM.

06-26-2006, 10:45 AM	#6
FrogMan Hattrick Moderator Join Date: Jan 2003 Location: Pintendre, Qc, Canada	what they all said, VLOOKUP is your friend... FM __________________ A Black Belt is a White Belt who refused to give up... follow my story: The real life story of a running frog...

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)