# Custpom Lookup formula not working.

#### countryfan_nt

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

### 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
LOOKUP requires the lookup array to be in ascending order.
You might try a different approach:
=MONTH(DATEVALUE(A13&1))

#### KRice

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

Replies
5
Views
74
Replies
10
Views
155
Replies
7
Views
118
Replies
10
Views
97
Replies
1
Views
115