# LOOKUP Formula not returning correct value from array

#### TukaranXL

##### New Member
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!

### Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

#### MrKowz

##### Well-known Member
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

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

#### TukaranXL

##### New Member
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

##### MrExcel MVP
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

##### Well-known Member
Or, you can use this formula instead :

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

Regards

Last edited:

#### kidwispa

##### Active Member
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...