Custpom Lookup formula not working.

countryfan_nt

Well-known Member
Joined
May 19, 2004
Messages
716
Hello friends, Hope all is well!

Please help me fix the formula below:

when I place Nov in A13; and end up getting 8!
when I enter Aug, I get: #N/A

=LOOKUP( I13, {"Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec"}, {"1","2","3","4","5","6","7","8","9","10","11","12"})

Please help, thank you very much in advance!
 

Some videos you may like

Excel Facts

How to create a cell-sized chart?
Tiny charts, called Sparklines, were added to Excel 2010. Look for Sparklines on the Insert tab.

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
LOOKUP requires the lookup array to be in ascending order.
You might try a different approach:
=MONTH(DATEVALUE(A13&1))
 

KRice

Well-known Member
Joined
Dec 9, 2003
Messages
842
Office Version
  1. 2019
Platform
  1. Windows
The formula I suggested is a more compact way to return the number of the month.
=MONTH(DATEVALUE(A13&1))

If you really want to use a lookup function, then consider something like this:
=VLOOKUP( A13, {"Jan",1;"Feb",2;"Mar",3;"Apr",4;"May",5;"Jun",6;"Jul",7;"Aug",8;"Sep",9;"Oct",10;"Nov",11;"Dec",12},2,FALSE)
VLOOKUP used with the FALSE option (4th argument) does an exact match and it does not require the lookup values to be in ascending order.

And if you really want to use the LOOKUP function, which requires a sorted (ascending) lookup array, then the arrays need to be manually sorted:
=LOOKUP( A13, {"Apr","Aug","Dec","Feb","Jan","Jul","Jun","Mar","May","Nov","Oct","Sep"}, {4,8,12,2,1,7,6,3,5,11,10,9})
 

Watch MrExcel Video

Forum statistics

Threads
1,114,035
Messages
5,545,634
Members
410,696
Latest member
JTrehan
Top