LOOKUP Formula not returning correct value from array

TukaranXL

New Member
Joined
May 10, 2011
Messages
15
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!
 

Some videos you may like

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
Joined
Jun 30, 2008
Messages
6,653
Office Version
365, 2016
Platform
Windows
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
Joined
Jun 19, 2002
Messages
63,651
LOOKUP requires the list in the first {} to be sorted alphabetically, then the items in the second {} arranged to match.
 

TukaranXL

New Member
Joined
May 10, 2011
Messages
15
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
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Dec 2, 2002
Messages
1,938
Office Version
2019
Platform
Windows
Or, you can use this formula instead :

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

Regards
 
Last edited:

kidwispa

Active Member
Joined
Mar 7, 2011
Messages
330
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...
 

Watch MrExcel Video

Forum statistics

Threads
1,102,288
Messages
5,485,912
Members
407,523
Latest member
Talicius

This Week's Hot Topics

Top