Extracting an item from a list in Excel

Question

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.

Answer 1

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

Answer 2

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)

Answer 3

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.

Answer 4

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.