Date within date range

[sindhuja]

Hi... can this be done using index function. While comparing the belwo i need to consider column A and E also.

Compare date in column F with the columns B and C and find whether date is with in the range of dates in column B and Column C provided considering the value in column A and column E.

spreadsheet with sample data has been attached for reference.

Kindly assist.:help

-Sindhuja

[Bob Phillips]

Try this ARRAY formula

=($E2=$A$2:$A$3)*($F2>=$B$2:$B$3)*($F2<=$C$2:$C$3)=1

[sindhuja]

Thanks Xld..

I tried the array formula but it is not provided the result, if it has more than one same value either in column A or column E.

i have attached the file for reference.

-sindhuja

[raji2678]

Best way is to use a macro. Loop through all the dates in the source list. If there is even a single one within the range, return true. This will be easy to maintain in the long run as well.

[sindhuja]

Can anyone please assist with the macro..:help:help

-Sindhuja

[raji2678]

PFA the solution in the attached sheet

[mohanvijay]

try this

SUMPRODUCT(--($A$2:$A$5=E2)*($B$2:$B$5<=F2)*($C$2:$C$5>=F2))>0

[Bob Phillips]

Thanks Xld..

I tried the array formula but it is not provided the result, if it has more than one same value either in column A or column E.

i have attached the file for reference.

-sindhuja

You need to array-enter it, Ctrl-Shift-Enter not just Enter.

I also missed a vital component in my formula, it should be

=SUM(($E2=$A$2:$A$17)*($F2>=$B$2:$B$17)*($F2<=$C$2:$C$17))>0

When you array enter it, you will see it encompassed by { }

[sindhuja]

Thank you Xld :)

The formula works for me. AS i said earlier i have more number of rows. I copy pasted the formula to around 10000 rows but it took more than 1 hour to calculate the results.

Is there a way to avoid the time delay.

Sindhuja

[sindhuja]

Thanks for the coding Raji...

I tried coding but the results were not as expected. Am not sure where i went wrong. The result shows only true though it is false. Please assist.

-Sindhuja

[raji2678]

In the sheet I sent, did the results tally? Beside coding, you will have to do your fair share of debugging as well,in order to find out what went wrong :-)

[sindhuja]

Hi Xld,

the formula works fine for me.. in addition i need the next column value (ie column D value) instead of true or false.

I tried but i didnt get the results i need.. can you please help me

-sindhuja

[Bob Phillips]

There is nothing in column D.

[sindhuja]

Yes in the attached sheet i have not mentioned any value. But the column D is the amount column.

-Sindhuja

[Bob Phillips]

Maybe this array formula

=IF(($E2=$A$2:$A$3)*($F2>=$B$2:$B$3)*($F2<=$C$2:$C$3)>0,INDEX($D$2:$D$3,MATCH(1,($E2=$A$2:$A$3)*($F2>=$B$2:$B$3)*($F2<=$C$2:$C$3),0)),"")

[sindhuja]

Please find the attached sheet for your reference. The results are not as per expected.
Kinldy do the needful.

-Sindhuja

[Haseeb A]

Hello Sindhuja,

Looks like you are on XL2007 or later, If so try SUMIFS.

=SUMIFS(D:D,A:A,F2,B:B,"<="&G2,C:C,">="&G2)
copy down.


Haseeb

[sindhuja]

Hello Haseeb,


Thanks !! But the results are not as expected.

Please help me .

-sindhuja

[Bob Phillips]

Is this any better?

=IF(NOT(ISNA(MATCH(1,($F2=$A$2:$A$21)*($G2>=$B$2:$B$21)*($G2<=$C$2:$C$21),0))),
INDEX($D$2:$D$21,MATCH(1,($F2=$A$2:$A$21)*($G2>=$B$2:$B$21)*($G2<=$C$2:$C$21),0)),"")

[Haseeb A]

...the results are not as expected...

Sindhuja, Can you please attach a sheet with expected results?