# Formula to lookup 2 criteria with dates and value

1. ## Formula to lookup 2 criteria with dates and value

Hello,

I will pls. need a formula to automatically distribute the value from another sheet in a monthly basis based on 2 criteria e.i. Invoice # and Invoice date.

I've attached a file for easy reference. Need formula in range C2:P7 (yellow highlight)

Ricky  Register To Reply

2. ## Re: Formula to lookup 2 criteria with dates and value

In D1 copied across:

=EDATE(C1,1)

Then in C2 copied across and down:

=IFERROR(IF(EOMONTH(\$B2,-1)+1=C\$1,INDEX(Sheet2!\$C\$2:\$C\$7,MATCH(Sheet1!\$A2,Sheet2!\$A\$2:\$A\$7,0)),""),"")  Register To Reply

3. ## Re: Formula to lookup 2 criteria with dates and value

Hello Mam Ali,

Thanks for your help! The formula works perfectly!

Regards
Ricky  Register To Reply

4. ## Re: Formula to lookup 2 criteria with dates and value

No problem.  Register To Reply

5. ## Re: Formula to lookup 2 criteria with dates and value

Sheet1

C2=IF(Sheet1!\$A2<>"",SUMPRODUCT((Sheet2!\$A\$2:\$A\$7=Sheet1!\$A2)*(MONTH(Sheet1!\$B2)=MONTH(Sheet1!C\$1))*(YEAR(Sheet1!\$B2)=YEAR(Sheet1!C\$1))*(Sheet2!\$C\$2:\$C\$7)),"")

copy across and down

for hide 0 (zero) custom format [=0]""  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 