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.

**Table of Contents**hide

## 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.

📌 ** 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**(argument) is mapped from the*lookup_value***US_States!$B$4:$C$55**(argument) array in the*table_array**“US_States”*worksheet. Next,**2**(argument) represents the column number of the lookup value. Lastly,*col_index_num***FALSE**(argument) refers to the*range_lookup***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.*

**Read More: ****How to Use VLOOKUP to Find State Abbreviations**

### 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 theargument that refers to the*lookup_value**“State of Florida”*. Following,**US_States!B5:B55**represents theargument from where the*lookup_array**“State”*is matched. Lastly,**0**is the optionalargument which indicates the*match_type***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 theargument which is the*array**“2 Letter Code”*. Lastly,**10**is theargument that indicates the row location.*row_num***Output → FL**

**Read More: ****How to Apply Abbreviation Using Formula in Excel**

### 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**(argument) is mapped from the*lookup_value***US_States!B5:B55**(argument) array. Lastly,*lookup_array***US_States!C5:C55**(argument) represents the returned array or range.*return_array***Output → FL**

**Read More: ****How to Convert Abbreviations to Words in Excel (2 Easy Methods)**

### 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.

- 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.

**Read More: ****How to Convert State Abbreviation to Name in Excel (3 Easy Ways)**

## 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**:

- 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.

## 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.

## Conclusion

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**.