Index and match with an additional condition (exce

Question

Continuing this topic, I would like to add one more condition. I have the following start, end, and action columns:

11/9/2007   10/10/2008  SELL
11/14/2008  5/29/2009   BUY
11/27/2009  2/5/2010    SELL
10/8/2010   4/8/2011    SELL

I also have target days (ex-weekends) starting from April 1, 2007 to today. I would like to do the following:

• If the target day is in the range of start and end dates AND action is SELL, then print out -1;
• If the target day is in the range of start and end dates AND action is BUY, then print out 1;
• If the target day is not in the range of start and end dates, then print out 0;

Any guidance/tips appreciated as always.

Answer 1

Continuing in the style of my answer to the earlier question you cited...

Add an index column to your input table containing the values 1, 2, 3,... The expression =SUMPRODUCT((target>=start)*(target<=end)*index) will indicate which row of your input table (if any) that target falls within. If the expression returns a value of 0 then the target does not fall within any of the date ranges. The expression relies on the date ranges not overlapping (so that a target can fall within at most 1 date range). If the date ranges can overlap then if a target falls within 2 or more date ranges the expression will return the sum of the indices of the ranges it falls in. This will be a problem since, for example, you won't be able to tell if a result of 3 means the target falls in the third date range or both of the first two.

Use the result from this expression as the second argument of an INDEX() function where the first argument is the action range. This will then deliver BUY, SELL or #N/A according to whether the target date falls within a buy range, a sell range or not in any range. Convert these results to your desired +1, -1 and 0 using whatever method you want.

Answer 2

I think this code will help you problem

Dim dStart As Date
Dim dEnd As Date
Dim dDate As Date
Dim iCol As Integer

iCol = 2

Do While Cells(iCol, 2).Value <> ""
    dStart = Format(Cells(iCol, 2).Value, "mm/dd/yyyy")
    dEnd = Format(Cells(iCol, 3).Value, "mm/dd/yyyy")
    if dDate > dStart and dDate < dEnd then
        if Cell(iCol,4).Value = "SELL" then
            printout -1
        else
            printout 1
        end if
    else
        printout 0
    end if
    iCol = iCol + 1
Loop

If you just only need excel function

=IF(AND(J3 >= F3,J3 <= G3, H3="SELL"),-1,IF(AND(J3 >= F3,J3 <= G3, H3="BUY"),1,0))