MATCHing a date within a range of dates

    I have a list of names in column B, amounts in column C, and dates in column D. None of the columns has only unique entries.


    Then in column A, I concatenate them.


    I would like to MATCH name&amt&date in column A, HOWEVER.....the date can vary from 5 days before to 5 days after.


    So my question is: how can I update MATCH("DOG"&54&42385,$A$2:$A$100,0) so that it returns the location in A of an entry with DOG, 54, and a date ranging from 42380-42391?


    SUMPRODUCT? Array formula? Something else?


    Thanks!


    (I'm using Excel 2003.)

    Re: MATCHing a date within a range of dates


    Are you trying to return a count of matches?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Re: MATCHing a date within a range of dates


    Quote from NBVC;777266

    Are you trying to return a count of matches?


    No, sorry for the confusion. I'd like to return exactly what MATCH returns, the location of the match in the list. In this case, it will return the location of a match anywhere within that range of dates.

    Re: MATCHing a date within a range of dates


    I would use the individual columns since you're going to need an array formula for the date range anyway...


    Try something like this:


    [COLOR="#0000FF"]=MATCH(1,INDEX(($B$2:$B$100="Dog")*($C$2:$C$100=54)*($D$2:$D$100>=DATE(2016,1,11))*($D$2:$D$100<=DATE(2016,1,22)),0),0)[/COLOR]

    Where there is a will there are many ways. Finding one that works for you is the challenge!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login