Iterate through array with date criteria

[webdes03]

I'm having some trouble with something I'm trying to put together in Excel with VB. I know what I need to do, but I'm having a hard time getting my head around it.

I need to create a function that will look at an array and find an occurrence within the current month and output the value of one of the columns.

Column A contains dates, B contains a keyword and C contains a numerical value. I want to find the occurance of the keyword where the date is within the current month, and output the numerical value.

I've tried doing it with VLookup's but it didn't like that, or I didn't do it right, so I wanted to just make a VB function. The array will be fixed all the time say $A$1:$A$400, so the function could probably have just one input parameter (the keyword), and return the numerical value.

Any thoughts? I'm not really a VB pro so I'm not quite sure how to start...

[mikerickson]

If your keyword is in D1, this formula will return the value from column C where
a)The date in column A is in the same month as today
b)The entry in column B matches the entry in cell D1.


=INDEX(C1:C400,MATCH(1,(B1:B400=D1)*((YEAR(A1:A400)+MONTH(A1:A400)/15)=(YEAR(TODAY())+MONTH(TODAY())/15)),0),1)

This formula needs to be confirmed with Ctrl-Shift-Enter (Cmd+Return for Mac).

If there are duplicate rows that meet the criteria, the first is returned.
If there is no row that meets the criteria, #NA is returned.

If you want a VB routine, you could apply Evaluate to the above.

[Bob Phillips]

An alternative


=INDEX(C2:C20,MATCH(1,(B2:B20=D1)*(TEXT(TODAY(),"mmyyyy")=TEXT(A2:A20,"mmyyyy")),0),1)

[webdes03]

Thank you for your help! This works great.

As another question, what would I need to do to make check to see if the B column contained the keyword, as opposed to an absolute match.

So "This is my keyword" and "keyword" would both return the number... Is that possible?

[Bob Phillips]

=INDEX(C2:C20,MATCH(1,(ISNUMBER(SEARCH(D1,B2:B20)))*(TEXT(TODAY(),"mmyyyy")=TEXT(A2:A20,"mmyyyy")),0),1)

[webdes03]

Thank you thank you.

You guys are great with this... I'm getting better, but I'm doing things with Excel that I never had the need to before.