Help with Lookup Aggregate formula to include multiple IF

[MarcBK]

Hello all,

I am using the following formula to lookup when the last stocktake date of product range has been done. This formula triggers its date off the keyword "Stocktake" in column F. It falls over when a random stocktake is done on a separate product though for instance if stocktake is done on product B at a different date to product A. So I need to work out how to include an if statement to show if product name is listed in column a then run the lookup formula.

=LOOKUP(2,1/($A$6:$A$1000=AGGREGATE(14,6,$A$6:$A$1000/($F$6:$F$1000="Stocktake"),1)),$A$6:$A$81000)

Product A In	=SUMIF($B$6:B1000,"Product A",$C$6:C1000)+SUMIF($B$6:B1000," Product A",$",$E$6:E1000)-$G$3	Product B In	=SUMIF($B$6:B1000,"Product B",$C$6:C1000)+SUMIF($B$6:B1000," Product B",$",$E$6:E1000)-$G$3
Product A Out	=SUMIF(B:B,"Product A",D:D)	Product B Out	=SUMIF(B:B,"Product B",D:D)
Last Stocktake date Product A	New Formula with IF Product A in column B then return last stocktake date of product A	Last Stocktake date Product B	New Formula with IF Product B in column B then return last stocktake date of product B

So what I am hoping to do is trigger it when product in column B matches by name. If Product A in Column B and Stocktake in Column F return date from Column A. I will need to seperate out the lookup for the different products as shown above but I do not know how to include the if statement for the product lookup in column B.

Date	Product	In From Supply	Out	In from Other	Notes
11/8/22	Product A	50
10/10/22	Product B	65
12/10/22	Product C	120
18/11/22	Product B		15
22/11/22	Product A		18
1/12/22	Product A		30
1/12/22	Product B			6	Stocktake
8/12/22	Product C			5	Stocktake
16/1/23	Product A			2	Stocktake

All help is greatly appreciated.

 

[Peter_SSs]

Is this what you are trying to do?

22 12 01.xlsm
	A	B	C	D	E	F	G	H	I
1	Date	Product	In From Supply	Out	In from Other	Notes		Product	Last Stocktake
2	11/08/2022	Product A	50					Product A	16/01/2023
3	10/10/2022	Product B	65					Product B	1/12/2022
4	12/10/2022	Product C	120					Product C	8/12/2022
5	18/11/2022	Product B		15
6	22/11/2022	Product A		18
7	1/12/2022	Product A		30
8	1/12/2022	Product B			6	Stocktake
9	8/12/2022	Product C			5	Stocktake
10	16/01/2023	Product A			2	Stocktake
Stocktake

Cell Formulas
Range	Formula
I2:I4	I2	=AGGREGATE(14,6,A$2:A$10/((B$2:B$10=H2)*(F$2:F$10="Stocktake")),1)

 

[MarcBK]

Is this what you are trying to do?

22 12 01.xlsm
	A	B	C	D	E	F	G	H	I
1	Date	Product	In From Supply	Out	In from Other	Notes		Product	Last Stocktake
2	11/08/2022	Product A	50					Product A	16/01/2023
3	10/10/2022	Product B	65					Product B	1/12/2022
4	12/10/2022	Product C	120					Product C	8/12/2022
5	18/11/2022	Product B		15
6	22/11/2022	Product A		18
7	1/12/2022	Product A		30
8	1/12/2022	Product B			6	Stocktake
9	8/12/2022	Product C			5	Stocktake
10	16/01/2023	Product A			2	Stocktake
Stocktake

Cell Formulas
Range	Formula
I2:I4	I2	=AGGREGATE(14,6,A$2:A$10/((B$2:B$10=H2)*(F$2:F$10="Stocktake")),1)

Kind of, but not quite.

My required formula to lookup product and return last date based on keyword 'stocktake' is in cell G4.

date is column A6:A1000
'stocktake' keyword could be found column anywhere in G6:G1000
Product in column B6:B1000

example: If Product A in B6 has keyword 'stocktake' in G6 then return date from A6

but then if later on in the sheet for Product A (for this example we will go Product A is in B380 and also has keyword stocktake in G380 then return date from A380 instead of the above A6. if that makes sense.

 

[Peter_SSs]

'stocktake' keyword could be found column anywhere in G6:G1000

Previously you said "Stocktake" was in column F.

In your sample data the dates are in chronological order down column A. Is that the case with your real data?
If so, then apart from slight changes to the placement and sizes of the ranges, in what way is my existing formula failing?

MarcBK.xlsm
	A	B					G	H	I
2
3								Product	Last Stocktake
4								Product A	21/02/2023
5	Date	Product					Notes
6	11/08/2022	Product A
7	10/10/2022	Product B
8	12/10/2022	Product C
9	18/11/2022	Product B
10	22/11/2022	Product A
11	1/12/2022	Product A
12	1/12/2022	Product B					Stocktake
13	8/12/2022	Product C					Stocktake
14	16/01/2023	Product A					Stocktake
15	25/01/2023	Product B
16	3/02/2023	Product C
17	12/02/2023	Product B
18	21/02/2023	Product A					Stocktake
19	2/03/2023	Product A
20
Stocktake

Cell Formulas
Range	Formula
I4	I4	=AGGREGATE(14,6,A$6:A$1000/((B$6:B$1000=H4)*(G$6:G$1000="Stocktake")),1)

 

[MarcBK]

Previously you said "Stocktake" was in column F.

In your sample data the dates are in chronological order down column A. Is that the case with your real data?
If so, then apart from slight changes to the placement and sizes of the ranges, in what way is my existing formula failing?

MarcBK.xlsm
	A	B					G	H	I
2
3								Product	Last Stocktake
4								Product A	21/02/2023
5	Date	Product					Notes
6	11/08/2022	Product A
7	10/10/2022	Product B
8	12/10/2022	Product C
9	18/11/2022	Product B
10	22/11/2022	Product A
11	1/12/2022	Product A
12	1/12/2022	Product B					Stocktake
13	8/12/2022	Product C					Stocktake
14	16/01/2023	Product A					Stocktake
15	25/01/2023	Product B
16	3/02/2023	Product C
17	12/02/2023	Product B
18	21/02/2023	Product A					Stocktake
19	2/03/2023	Product A
20
Stocktake

Cell Formulas
Range	Formula
I4	I4	=AGGREGATE(14,6,A$6:A$1000/((B$6:B$1000=H4)*(G$6:G$1000="Stocktake")),1)

but how does this show if product B or product C dates?

 

[MarcBK]

Oh forget me. Moment of terrible judgement. Just actually look at it. Whoops.

 

[Peter_SSs]

So, all good now?

 

[Peter_SSs]

@MarcBK
I have removed the 'Mark as solution' mark from post #7 as clearly it is not the solution to your question. That should be used to mark the actual post that contains the solution to you question to help future readers. If no post contains the solution then don't mark any.