VLOOKUP to write name of month in a cell

[BeerKing]

Hi everyone

I'm trying to get this VLOOKUP formula to work. I'm trying to get Excel to "pick up" the name of a certain month from another sheet. In Sheet1 I have Month# from 1-12 in cells A2:A13 and Monthname from January to December in cells B2:B13.

In Sheet2 I have Month# for 4 years (4 times 1-12) in Cells A2:A49, and I want Excel to fill in the correct Monthname in cells B2:B49. It works with the first 12 months, but the second time the formula returns #I/T.

This is the formula (written in Sheet2, cell B2):

Please Login or Register  to view this content.

Thanks

[NBVC]

Should it be?

=VLOOKUP($A$2;Sheet1!$A$2:$B$13;2;FALSE)

[JBeaucaire]

Try this:

=INDEX(Sheet1!$B$2:$B$13; IF(MOD(ROW(A1);12)=0; 12; MOD(ROW(A1);12)))

[alansidman]

Try changing your semi-colons ; to commas (,).

Alan

[NBVC]

Alan,

Not sure if that is the problem, it depends on the regional settings, but ; is used instead of commas sometimes. Denmark and other European and South American countries tend to use those regional settings more often.

[alansidman]

@NBVC
Got it. Thanks. Hindsight, I think you solved in your first post. Missed that he had a range and not a single cell for the criteria.

Alan

[BeerKing]

Sorry, but none of the suggestions seems to work.

And yeah, in danish Excel we use semi-colon in formulas.

[NBVC]

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

[Cutter]

Why don't you just have this in B14 (if the first 12 worked)

=B14

dragged down to B49

But making a minor change to NBVC's formula in Post 2 should solve your problem

He gave you
=VLOOKUP($A$2;Sheet1!$A$2:$B$13;2;FALSE)
and should have been
=VLOOKUP($A2;Sheet1!$A$2:$B$13;2;FALSE)
(removed $ in front of 2 for the lookup value)

[JBeaucaire]

If the months January-December are listed on Sheet1 in B2:B13, then my formula placed anywhere on sheet2, then copied down, then the INDEX() formula I gave should display those months in a cycle. Try it again.

========
NOTE: If you're just trying to get Jan-Dec to appear sequentially for as long as you copy down, Excel will do that on its own.

1) Type January in a cell
2) Grab the bottom right corner of that cell and drag down...the other months will appear on their own

[NBVC]

But making a minor change to NBVC's formula in Post 2 should solve your problem

He gave you
=VLOOKUP($A$2;Sheet1!$A$2:$B$13;2;FALSE)
and should have been
=VLOOKUP($A2;Sheet1!$A$2:$B$13;2;FALSE)
(removed $ in front of 2 for the lookup value)

Thanks Cutter

[BeerKing]

Thanks for the help guys, but I can't spend another minute on this problem now, I have an exam tomorrow, and I'm not even sure that I'll need this specific knowledge.

Anyways, I solved it with another formula. I have another sheet with a full date (ddmmyyyy) so I solved it with

Please Login or Register  to view this content.

But again, thanks for helping, I'll study it closer when I have the time.