Exceljet

Quick, clean, and to the point

Abbreviate state names

Excel formula: Abbreviate state names
Generic formula 
=VLOOKUP(name,states,2,0)
Explanation 

To convert full state names to their two letter abbreviation (i.e. Minnesota > MN), you can use a simple formula based on VLOOKUP. In the example shown, the formula in E5 is:

=VLOOKUP(C5,states,2,0)

Where "states" is the named range G5:H55.

How this formula works

This formula relies on a table with columns for both the full state name and the 2-letter abbreviation. Because we are using VLOOKUP, the full name must be in the first column. For simplicity, the table has been named "states".

VLOOKUP is configured to get the lookup value from column C. The table array is the named range "states", the column index is 2, to retrieve the abbreviation from the second column). The final argument, range_lookup, has been set to zero (FALSE) to force an exact match.

=VLOOKUP(C5,states,2,0)

VLOOKUP locates the matching entry in the "states" table, and returns the corresponding 2-letter abbreviation.

Generic mapping

This is a good example of how VLOOKUP can be used to convert values using a lookup table. This same approach can be used to lookup and convert many other types of values. For example, you could use VLOOKUP to map numeric error codes to human readable names.

Reverse lookup

What if you have a state abbreviation, and want to lookup the full state name using the lookup table in the example? In that case, you'll need to switch to INDEX and MATCH. With a lookup value in A1, this formula will return a full state name with the lookup table as shown:

=INDEX(G5:G55,MATCH(A1,H5:H55,0))

If you want to use the same named range "states" you can use this version to convert a 2-letter abbreviation to a full state name.

=INDEX(INDEX(states,0,1),MATCH(A1,INDEX(states,0,2),0))

Here, we use INDEX to return whole columns by supplying a row number of zero. This is a cool and useful feature of the INDEX function: if you supply zero for row, you get whole column(s) if you supply zero for column, you get whole row(s).

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.

Excel foundational video course
Excel Pivot Table video training course
Excel formulas and functions video training course
Excel Shortcuts Video Course
Excel Charts video training course
Video training for Excel Tables