LOOKUP Formula not returning correct value from array

[TukaranXL]

Hi,

I have a spreadsheet with some data, alongside each entry in the list is a month in "MMM" format. I want to lookup this value and return the full month using a LOOKUP formula. It used to use a VLOOKUP from another table but I want to get rid of this additional table.

Basically my formula is: "=LOOKUP(C1,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},{"JANUARY","FEBRUARY","MARCH","APRIL","MAY","JUNE","JULY","AUGUST","SEPTEMBER","OCTOBER","NOVEMBER","DECEMBER"})"

If C1 contains "APR" the formula returns "#N/A", if it contains "MAY" it returns "AUGUST"(!). "JAN" returns "JANUARY" but that's as far as it seems to work.

Am I missing something really obvious? I've done a similar lookup before which took the month as a number and returned the month as a string (i.e. 2 = FEBRUARY, 4 = APRIL etc) and this worked fine.

Cheers!

 

[MrKowz]

The Lookup_Array must be in ascending/alphabetical order to use LOOKUP.

Try adjusting this to INDEX/MATCH, which can accommodate exact match without regard to order:

=INDEX({"JANUARY","FEBRUARY","MARCH","APRIL","MAY","JUNE","JULY","AUGUST","SEPTEMBER","OCTOBER","NOVEMBER","DECEMBER"},MATCH(C1,{"JAN","FEB","MAR","APR","MAY","JUN","JUL","AUG","SEP","OCT","NOV","DEC"},0))

 

[VoG]

LOOKUP requires the list in the first {} to be sorted alphabetically, then the items in the second {} arranged to match.

 

[TukaranXL]

Perfect!

I've rejigged the months into alphabetical order and it works fine now:

=LOOKUP(C1,{"APR","AUG","DEC","FEB","JAN","JUL","JUN","MAR","MAY","NOV","OCT","SEP"},{"APRIL","AUGUST","DECEMBER","FEBRUARY","JANUARY","JULY","JUNE","MARCH","MAY","NOVEMBER","OCTOBER","SEPTEMBER"})

Thanks for the quick replies!

 

[barry houdini]

Here's another option

=HLOOKUP(C1&"*",UPPER(TEXT(DATE(,{1,2,3,4,5,6,7,8,9,10,11,12},1),"mmmm")),1,0)

 

[bosco_yip]

Or, you can use this formula instead :

=UPPER(TEXT("1/"&C1,"mmmm"))

Regards

 

[kidwispa]

Perfect!

I've rejigged the months into alphabetical order and it works fine now:

=LOOKUP(C1,{"APR","AUG","DEC","FEB","JAN","JUL","JUN","MAR","MAY","NOV","OCT","SEP"},{"APRIL","AUGUST","DECEMBER","FEBRUARY","JANUARY","JULY","JUNE","MARCH","MAY","NOVEMBER","OCTOBER","SEPTEMBER"})

Thanks for the quick replies!

One thing I've just picked up when I use the above formula is that even if you key in incorrect responses (ie APS instead of APR, JEN instead of JAN) it still returns the closest answer (APRIL and JANUARY for the examples given). Not sure if this will be a problem for you as not sure how your data is populated but perhaps the Index/Match formula suggested by MrKowz would be more accurate...