Get Value based on date criteria

[simply_shailesh]

I have 2 tablets 1st is reference and second output required

1st table is like

Date Product Price
1-1-20 ProductA 1000
1-1-20 ProductB 1100
4-1-20 ProductA 1010
6-1-20 ProductA 990
Contains 25k rows

Second Table

Date Product Price Available
1-1-20 ProductA 1000
3-1-20 ProductB 1100
4-1-20 ProductA 1010
5-1-20 ProductA 1010
7-1-20 ProductA 990
Contain 60k row

I need arrayformula for google sheets....

Kindly help...

 

[dave3009]

Hi

Presumably you want to test if the date and product on sheet2 has a match on sheet1, and if so it's available?

Book1
	A	B	C	D	E	F	G	H	I
1	Date	Product	Price		Date	Product	Price	Available
2	01/01/2020	ProductA	1000		01/01/2020	ProductA	1000	Yes
3	01/01/2020	ProductB	1100		03/01/2020	ProductB	1100	No
4	04/01/2020	ProductA	1010		04/01/2020	ProductA	1010	Yes
5	06/01/2020	ProductA	990		05/01/2020	ProductA	1010	No
6					07/01/2020	ProductA	990	No
7
Sheet1

Cell Formulas
Range	Formula
H2:H6	H2	=IF(COUNTIFS(A:A,E2,B:B,F2),"Yes","No")

 

[simply_shailesh]

Hi

Presumably you want to test if the date and product on sheet2 has a match on sheet1, and if so it's available?

Book1
	A	B	C	D	E	F	G	H	I
1	Date	Product	Price		Date	Product	Price	Available
2	01/01/2020	ProductA	1000		01/01/2020	ProductA	1000	Yes
3	01/01/2020	ProductB	1100		03/01/2020	ProductB	1100	No
4	04/01/2020	ProductA	1010		04/01/2020	ProductA	1010	Yes
5	06/01/2020	ProductA	990		05/01/2020	ProductA	1010	No
6					07/01/2020	ProductA	990	No
7
Sheet1

Cell Formulas
Range	Formula
H2:H6	H2	=IF(COUNTIFS(A:A,E2,B:B,F2),"Yes","No")

All are available so no yes or no

 

[dave3009]

You aren't being clear, what is the expected result from your example?

 

[simply_shailesh]

I have 2 tablets 1st is reference and second output required

1st table is like

Date Product Price
1-1-20 ProductA 1000
1-1-20 ProductB 1100
4-1-20 ProductA 1010
6-1-20 ProductA 990
Contains 25k rows

Second Table

Date Product Price Available
1-1-20 ProductA 1000
3-1-20 ProductB 1100
4-1-20 ProductA 1010
5-1-20 ProductA 1010
7-1-20 ProductA 990
Contain 60k row

I need arrayformula for google sheets....

Kindly help...

*Price-Available can be read as Price_to_be

 

[dave3009]

So more like this?

Book1
	A	B	C	D	E	F	G	H
1	Date	Product	Price		Date	Product	Price-Available
2	01/01/2020	ProductA	1000		01/01/2020	ProductA	1000
3	01/01/2020	ProductB	1100		03/01/2020	ProductB	1100
4	04/01/2020	ProductA	1010		04/01/2020	ProductA	1010
5	06/01/2020	ProductA	990		05/01/2020	ProductA	1010
6					07/01/2020	ProductA	990
7
Sheet1

Cell Formulas
Range	Formula
G2:G6	G2	=INDEX(FILTER($C$2:$C$5,$B$2:$B$5=F2), MATCH(E2,FILTER($A$2:$A$5,$B$2:$B$5=F2),1))

 

[simply_shailesh]

You aren't being clear, what is the expected result from your example?

Want to fill the price available column of table to from table one depend on the product and date

Date Product. Price Available
1-1-20 ProductA 1000
3-1-20 ProductB 1100
4-1-20 ProductA 1010
5-1-20 ProductA 1010
7-1-20 ProductA 990


This is was I am looking for dear....

 

[simply_shailesh]

Scenario 2 -
In this I have start date and end date

I have 2 tables 1st is reference and second output required

1st table is like

StartDate End Date Product Price
1-1-20 3-1-2020 ProductA 1000
1-1-20 31-1-2020 ProductB 1100
4-1-20 5-1-2020 ProductA 1010
6-1-20 20-1-2020 ProductA 990
Contains 25k rows

Second Table

Date Product Price Available
1-1-20 ProductA 1000
3-1-20 ProductB 1100
4-1-20 ProductA 1010
5-1-20 ProductA 1010
7-1-20 ProductA 990
Contain 60k row

Currently i am doing this with index, match with maxifs. But i need arrayformula for google sheets....

Kindly help...

 

[Peter_SSs]

Duplicate to: Sum If between start date and end date

In future,

• please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.
• post Google Sheets questions in the forum marked for such:
  
  
  

In relation to your question here, I have closed this thread so please continue in the linked thread.