# Extracting an item from a list in Excel

How can I get data from a enumerated list?

For example, if I enter 3 in cell `A1`, I would like cell `B1` to display "march".

``````A1  3
B1  =FX(A1,"january,february,march,april,may")
``````

Any function available for this? The list can be anything.

-

## 4 Answers

Here are a few methods you can try:

### METHOD #1: TEXT() FUNCTION

This will only work if you're working with months. In cell B1, enter:

``````=TEXT(DATE(2000,A1,1),"mmmm")
``````

You may replace 2000 with any year number. If you want to use the current year, replace 2000 with `YEAR(NOW())`, although it shouldn't really matter which year you use.

### METHOD #2: CHOOSE() FUNCTION

In cell B1, enter:

``````=CHOOSE(A1,"January","February","March","April","May","June","July","August",
"September","October","November","December")
``````

Typing this formula can be tiresome. To manage this a bit more quickly, you may try the ff. steps:

1. Enter "January" in a blank cell and drag the Fill Handle 12 cells to the right until the cells are populated with months.

2. In cell B1, enter `=` and select the cells that contain the month names.

3. Press F9. You'll get a formula that looks like this:

```={"January","February","March","April","May","June","July","August"," September","October","November","December"}```

4. Edit the formula to incorporate the `CHOOSE()` function.

### METHOD #3: INDEX() and a Named Range

1. Create a named range. To do this, go to Formulas > Define Name.
2. Enter your preferred name in the Name field. In my case, I used months.
3. Enter the ff. formula in the Refers to field (You may also use a helper range** for this):

```={"January";"February";"March";"April";"May";"June";"July";"August"; "September";"October";"November";"December"}```

You'll have something like this:

4. In cell B1, use the ff. formula:

`=INDEX(months,A1,1)`

**For example, if you choose to enter the months in cells D1 to D12. In the Refers to field, enter `=D1:D12`.

-
Is that all? Just kidding, very nice answer. +1. –  Doug Glancy Jul 7 '12 at 14:45
Haha! Thanks! :D I think there are better methods than these 3 but I can't put my finger on it. –  Ellesa Jul 7 '12 at 14:55
Nice! for fun you can also use this formula for months, =TEXT(A1*29,"mmmm") –  barry houdini Jul 7 '12 at 20:23

This is simply a key,value definition with a key lookup (and getting back the value). Use another sheet for that enumerated list, and use VLOOKUP on the first sheet.

In this case the key is `3` and the value is `march`, so this would be on Sheet2:

``````A1 = 3
B1 = "march"
``````

and then on Sheet1:

``````A1 = 3
B1 = VLOOKUP(A1;Sheet1!A:B;2;FALSE)
``````
-

You can use the vlookup function.
Enter in Sheet 2
A1 1
B1 Jan
A2 2
B2 Feb
A3 3
B3 Mar

etc.

Now enter the formula =VLOOKUP(A1,Sheet2!\$A\$1:\$B\$12,2,FALSE)
In B1 cell of Sheet1.
Copy this formula to cells below B1.
Now enter the month values in cells below A1. These will be decoded in the corresponding cells in column B.

-

The other answers provided so far suggest you reorganise your data to a format better suited to Excel's inbuilt functions and there are good reasons for doing so, but if you really must have your data in a comma-separated text string then the following will work:

``````A1   3
A2   january,february,march,april,may
A3   =IFERROR(MID(A2,FIND("_",SUBSTITUTE(","&A2&",",",","_",A1)),FIND("_",SUBSTITUTE(","&A2&",",",","_",A1+1))-FIND("_",SUBSTITUTE(","&A2&",",",","_",A1))-1),"[Out of bounds]")
``````

You can substitute the "_" for any other character that won't appear in your list.

-