Combining Vlookup with Month Dates to make a Commodity Ticke

sophia

Board Regular
Joined
Dec 27, 2005
Messages
79
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! :)
 

Some videos you may like

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
Joined
Mar 10, 2004
Messages
19,403
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,118,673
Messages
5,573,588
Members
412,537
Latest member
Mohamed_5966
Top