Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question

4 Answers 4

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.

share|improve this answer

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)
share|improve this answer

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.

share|improve this answer

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.

    enter image description here

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

    enter image description here

  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:

    enter image description here

  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.

share|improve this answer
    
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
1  
Nice! for fun you can also use this formula for months, =TEXT(A1*29,"mmmm") –  barry houdini Jul 7 '12 at 20:23

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.