How to Find 2 Letter State Abbreviations in Excel (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Indeed, while working in Excel we may need to obtain the abbreviation of the State names. Though there is no dedicated function to do this, however, Excel has several nifty tricks up its sleeve! In this article, we’ll demonstrate the 4 quick and easy ways to find 2 letter state abbreviations in Excel. Moreover, we’ll also learn to make a list of state abbreviations in Excel.


How to Find 2 Letter State Abbreviations in Excel: 4 Ways

First and foremost, let’s consider the List of Clientele dataset shown in the B4:D14 cells containing the “Company Name”, “Address”, and “State”. Here, we want to insert a column with the 2 letter state abbreviations, hence, let’s see each method in detail and with the necessary illustrations.

Dataset for 2 letter state abbreviations excel

Now, to obtain the 2 letter abbreviations in Excel we need a look-up table containing the full names of all the “State” and the corresponding abbreviated “2 Letter Code”. In this case, this lookup table resides in the “US_States” worksheet.

Lookup table for 2 letter state abbreviations excel

Lookup table continues

Here, we have used the Microsoft Excel 365 version; you may use any other version according to your convenience.


1. Using VLOOKUP Function

First of all, let’s start with one of the most useful functions in Excel. Yes, we’re talking about the VLOOKUP function which returns a value from a look-up table based on the specified rows and columns. So, let’s see it in action.

📌 Steps:

  • Initially, go to the E5 cell >> enter the formula given below.

=VLOOKUP(D5,US_States!$B$4:$C$55,2,FALSE)

Here, the D5 cell refers to the “State of Florida” while the B4:C55 cells indicate the look-up table in the “US_States” worksheet.

Formula Breakdown:

  • VLOOKUP(D5,US_States!$B$4:$C$55,2,FALSE) → looks for a value in the left-most column of a table and then returns a value in the same row from a column you specify. Here, D5 ( lookup_value argument) is mapped from the US_States!$B$4:$C$55 (table_array argument) array in the “US_States” worksheet. Next, 2 (col_index_num argument) represents the column number of the lookup value. Lastly, FALSE (range_lookup argument) refers to the Exact match of the lookup value.
    • Output → FL

📃 Note: Please make sure to use Absolute Cell Reference by pressing the F4 key on your keyboard.

2 letter state abbreviations excel with VLOOKUP function


2. Utilizing INDEX and MATCH Functions

Alternatively, we can also combine the INDEX and MATCH functions to output the abbreviated state names from the look-up table. So, just follow along.

📌 Steps:

  • To begin with, move to the E5 cell >> insert the expression into the Formula Bar.

=INDEX(US_States!C5:C55,MATCH(D5,US_States!B5:B55,0))

In this case, the B5:B55 and C5:C55 range of cells point to the “State” and “2 Letter Code” columns in the “US_States” worksheet.

Formula Breakdown:

  • MATCH(D5,US_States!B5:B55,0) → returns the relative position of an item in an array matching the given value. Here, D5 is the lookup_value argument that refers to the “State of Florida”. Following, US_States!B5:B55 represents the lookup_array argument from where the “State” is matched. Lastly, 0 is the optional match_type argument which indicates the Exact match criteria.
    • Output → 10
  • INDEX(US_States!C5:C55,MATCH(D5,US_States!B5:B55,0)) → becomes
    • INDEX(US_States!C5:C55,10) → returns a value at the intersection of a row and column in a given range. In this expression, the US_States!C5:C55 is the array argument which is the “2 Letter Code”. Lastly, 10 is the row_num argument that indicates the row location.
    • Output → FL

2 letter state abbreviations excel with INDEX and MATCH functions


3. Applying XLOOKUP Function

For one thing, the XLOOKUP function succeeds the VLOOKUP function by eliminating its shortcomings and yielding the corresponding item from an array or look-up table.

📌 Steps:

  • In the first place, copy and paste the following equation in the E5 cell.

=XLOOKUP(D5,US_States!B5:B55,US_States!C5:C55)

For instance, the D5 cell refers to the “State of Florida”, whereas the B5:B55 and C5:C55 range of cells indicate the “State” and “2 Letter Code” columns in the “US_States” worksheet.

Formula Breakdown:

  • XLOOKUP(D5,US_States!B5:B55,US_States!C5:C55) → searches a range or an array for a match and returns the corresponding item from a second range or array. By default, an exact match is used. Here, D5 ( lookup_value argument) is mapped from the US_States!B5:B55 (lookup_array argument) array. Lastly, US_States!C5:C55 (return_array argument) represents the returned array or range.
    • Output → FL

2 letter state abbreviations excel with XLOOKUP function


4. Employing Named Range

Last but not least, we can apply Excel’s Named Range feature to make the formulas easier to understand and avoid entering direct cell references from other worksheets. It’s simple and easy, so just follow the steps.

📌 Steps:

  • First, select the B5:C55 cells in the “US_States” worksheet >> in the Name Box type in “States” as shown in the animated GIF below.

Defining Named Ranges

  • Second, click on the E5 cell >> enter the following formula.

=VLOOKUP(D5,States,2,FALSE)

For example, the D5 cell indicates the “State of Florida” while the “States” is the Named Range represents the look-up table in the “US_States” worksheet.

Using VLOOKUP function and Named Ranges


How to Make a List of State Abbreviations in Excel

Moreover, in the following sections, we’ll discuss the process to make a list of state abbreviations in Excel using the Text Import wizard.

📌 Steps:

How to Make a List of State Abbreviations in Excel

  • Next, launch Microsoft Excel >> jump to the Data tab >> click the Get Data drop-down >> in the Legacy Wizards, select the From Text (Legacy) option.

Legacy Text Import wizard

  • Afterward, select the CSV file from the file explorer >> click the Open button.

Browsing for CSV file

  • Not long after, check My data has headers >> press the Next button.

Text import wizard step 1

  • Following this, check the Comma Delimiter >> click on Next.

Choosing Comma delimiter

  • In turn, hit the Finish button to complete the process.

Text import wizard step 3 Finish Import

  • Later, select the cell reference to paste the data, here it is the B4 cell.

Entering a cell reference

Now, the GIF below displays the above steps in real time.

Text import wizard steps in GIF

Eventually, the final results should look like the screenshot shown below.

List of State Abbreviations in Excel


Things to Remember

As a note, there are a few important things to remember when using the VLOOKUP function.

  • First, the VLOOKUP function “Never” looks for the data to its left, otherwise, the function returns the #N/A error.
  • Second, the function will return an error if we enter a value less than 1 as the column index number.

Practice Section

We have provided a Practice section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Practice Section


Download Practice Workbook


Conclusion

To sum up, we hope this article helps you understand how to find 2 letter state abbreviations in Excel. Feel free to leave a comment below.


<< Go Back to Excel Abbreviation | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Eshrak Kader
Eshrak Kader

Eshrak Kader is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He boasts a rich background in Microsoft Office Suite, and over the past year, he has authored over 120 insightful articles for the ExcelDemy. Currently, Eshrak leads a team of 5 in the ExcelDemy Video project and enjoys problem-solving and making videos on Excel. Eshrak’s passion for continuous learning underscores his commitment to excellence in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo