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.
Download Practice Workbook
4 Ways to Find 2 Letter State Abbreviations in Excel
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.
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.
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.
- Initially, go to the E5 cell >> enter the formula given below.
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.
- 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. Utilizing INDEX and MATCH Functions
- To begin with, move to the E5 cell >> insert the expression into the Formula Bar.
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.
- 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
3. Applying XLOOKUP Function
- In the first place, copy and paste the following equation in the E5 cell.
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.
- 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
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.
- 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.
- Second, click on the E5 cell >> enter the following formula.
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.
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.
- At the very beginning, proceed to the WorldPopulationReview website >> click the CSV button to download the CSV file.
- 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.
- Afterward, select the CSV file from the file explorer >> click the Open button.
- Not long after, check My data has headers >> press the Next button.
- Following this, check the Comma Delimiter >> click on Next.
- In turn, hit the Finish button to complete the process.
- Later, select the cell reference to paste the data, here it is the B4 cell.
Now, the GIF below displays the above steps in real time.
Eventually, the final results should look like the screenshot shown below.
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.
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.
To sum up, we hope this article helps you understand how to find 2 letter state abbreviations in Excel. Now, feel free to leave a comment below, in addition, you can read more articles like this on our website ExcelDemy.