Combining Vlookup with Month Dates to make a Commodity Ticke

[sophia]

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!

 

[Domenic]

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))

Click Add

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)

Click Add

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))

Adjust the sheet references accordingly.

Hope this helps!