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!
 

Excel Facts

When they said...
When they said you are going to "Excel at life", they meant you "will be doing Excel your whole life".
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))
 
Upvote 0
LOOKUP requires the list in the first {} to be sorted alphabetically, then the items in the second {} arranged to match.
 
Upvote 0
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!
 
Upvote 0
Or, you can use this formula instead :

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

Regards
 
Last edited:
Upvote 0
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...
 
Upvote 0

Forum statistics

Threads
1,213,521
Messages
6,114,104
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top