# Combining Vlookup with Month Dates to make a Commodity Ticke

#### sophia

##### Board Regular
Hi. I always get confused when working with date stuff in Excel. Arggggg! I am also not super advanced, but really trying to get the hang of it So here is my problem:

On shest 1, in cells B4...C15 I have the Month Codes for Commodity Contracts and the corresponding months. So it looks like this:

F January
G February
H March
J April
K May
M June
N July
Q August
U September
V October
X November
Z December

On sheet 2 I have a list of Commodities with Root Codes and Active Months. So for example line 4 (columns a,b c) says:

GJMQVZ Live Cattle (CME) LC

Column 1 (the "GJMQVZ") are the months for that contract. LC (or column c) is the root code. What I am trying to accomplish is this: In column D I want an expression that looks at today's date (ie, dec 27) and says "hmmm, the next (most soon upcoming month) active contract for cattle LC is G, or Febuary. Output = LCg"

In other words, if today were feb 10, the next month active would be J, or (via the lookup) April. So, I need to look at today's date somehow, and only care about the month, look at the following month, or one after or one after and match it the months of the contract to spit out the Commodity Code + the right month code. Did that make any sense I hope this wasn't to mangled an explanation of whta I want to do! Desperate for help and hoping you excel gods have an idea of how to do this, thx!

### Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

#### Domenic

##### MrExcel MVP
Try the following...

1) First define the following references:

Select Sheet2!D4

Insert > Name > Define

Name: Array1

Refers to:

=MONTH(DATE(YEAR(TODAY()),MONTH(TODAY())+{1,2,3,4,5,6,7,8,9,10,11,12},1))

Name: Array2

Refers to:

=LOOKUP(MID(Sheet2!\$A4,ROW(INDIRECT("1:"&LEN(Sheet2!\$A4))),1),Sheet1!\$B\$4:\$B\$15,ROW(Sheet1!\$B\$4:\$B\$15)-ROW(Sheet1!\$B\$4)+1)

Name: MonthNum

Refers to:

=INDEX(Array1,MATCH(TRUE,ISNUMBER(MATCH(Array1,Array2,0)),0))

Click Ok

2) Then, enter the following formula in D4 and copy down, if necessary:

=\$C4&LOWER(INDEX(Sheet1!\$B\$4:\$B\$15,MonthNum))

Hope this helps!

Replies
3
Views
184
Replies
9
Views
123
Replies
5
Views
126
Replies
1
Views
292
Replies
1
Views
151