Find a value based on two criteria in other columns

[Yamezz]

I have a table of data with headers in A2:AB2 and actual data in A3:AB15. Column A is a listing of Product Names and columns K:AB contain alternating stocktake numbers and the date that stocktake was done.
From this data, I need to return the latest stocktake date in a particular row that meets two criteria.
If cell A20 contains the Product Name of interest, I need to find the row where this Product Name appears AND "X" appears in C3:C10 ("X" is text)
There may be multiple instances of A20, but only one of these will also have "X" in column C.
From the row identified above, find the MAX value in that row (this will be a date)

I'd be grateful for any help.

 

[Yamezz]

Does anyone have any suggestions for me?

 

[Peter_SSs]

Does anyone have any suggestions for me?

A suggestion would likely come much quicker if you gave some small representative sample data with XL2BB and explained what result(s) you would want from that data and why.

 

[Yamezz]

Hello Peter. Thanks for clicking on my question.

I have anonymised the data and included a mini sheet.

Sales and Production Planning.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB
2	Product	Batch	Current	Blah	Blah2	Blah3	Blah4	Blah5	Blah6	Blah7	Number	Date	Number2	Date3	Number4	Date5	Number6	Date7	Number8	Date9	Number10	Date11	Number12	Date13	Number14	Date15	Number15	Date17
3	Apple	14	X	Blah	Blah	Blah	Blah	Blah	Blah	Blah	1,608	06-Mar-18	400	30-Jun-22	294	31-Aug-22	257	26-Oct-22	97	05-May-23	54	24-Jun-23
4	Apple	16		Blah	Blah	Blah	Blah	Blah	Blah	Blah	1,620	12-Mar-19	1,345	30-Jun-22	1,300	31-Aug-22	1,338	26-Oct-22	1,108	05-May-23	1,111	01-Jul-23
5	Apple	17		Blah	Blah	Blah	Blah	Blah	Blah	Blah	1,905	01-Mar-19	1,905	30-Jun-22	1,905	31-Aug-22	1,905	26-Oct-22	1,883	05-May-23	1,890	29-Jun-23
6	Banana	22	X	Blah	Blah	Blah	Blah	Blah	Blah	Blah	1,536	15-Feb-23	1,259	05-May-23	1044	29-Jun-23
7	Cherry	18	X	Blah	Blah	Blah	Blah	Blah	Blah	Blah	2,292	17-Dec-21	1,705	30-Jun-22	1,438	31-Aug-22	1,333	26-Oct-22	292	05-May-23	940	29-Jun-23
8	Grape	17	X	Blah	Blah	Blah	Blah	Blah	Blah	Blah	8,976	05-Nov-19	2,952	30-Jun-22	2,652	31-Aug-22	2,395	26-Oct-22	1,876	05-May-23	1,536	24-Jun-23
9	Grape	19		Blah	Blah	Blah	Blah	Blah	Blah	Blah	204	1-Jul-23	200	07-Jul-23
10	Pear	21	X	Blah	Blah	Blah	Blah	Blah	Blah	Blah	2,500	23-Apr-22	2,149	30-Jun-22	1,768	27-Aug-22	1,472	26-Oct-22	798	05-May-23	523	29-Jun-23
11	Pear	22		Blah	Blah	Blah	Blah	Blah	Blah	Blah	1,368	1-Jul-23
12	Strawberry	18	X	Blah	Blah	Blah	Blah	Blah	Blah	Blah	1,548	23-Dec-20	811	30-Jun-22	240	31-Aug-22	90	26-Oct-22	227	05-May-23	66	01-Jul-23
13	Orange	19	X	Blah	Blah	Blah	Blah	Blah	Blah	Blah	1,680	20-May-22	1,672	30-Jun-22	1,672	31-Aug-22	1,549	26-Oct-22	1,656	05-May-23	1,255	24-Jun-23
14	Blueberry	20	X	Blah	Blah	Blah	Blah	Blah	Blah	Blah	2,706	17-Dec-21	1,737	30-Jun-22	1,271	31-Aug-22	1,237	26-Oct-22	1,275	05-May-23	885	29-Jun-23
15	Blueberry	21		Blah	Blah	Blah	Blah	Blah	Blah	Blah	143	1-Jul-23
16
17
18
19	Product	Last Stocktake
20	Apple	24-Jun-23
Stock (2)

The formula in B20 should return 24-Jun-23. This is determined by checking the table for the Product of interest (Apple), determining which batch is the current one (designated by an "X" in column C) and looking across that row to find the date of last stocktake.

 

[Peter_SSs]

Thanks for the XL2BB sample data and explanation.
Try this (but check the name of your table)

=IFERROR(LOOKUP(9^9,INDEX(Table1,AGGREGATE(15,6,(ROW(Table1)-ROW(Table1[#Headers]))/((Table1[Product]=A19)*(Table1[Current]="X")),1),0)),"")

 

[Yamezz]

Thanks for the XL2BB sample data and explanation.
Try this (but check the name of your table)

=IFERROR(LOOKUP(9^9,INDEX(Table1,AGGREGATE(15,6,(ROW(Table1)-ROW(Table1[#Headers]))/((Table1[Product]=A19)*(Table1[Current]="X")),1),0)),"")

Thank you Peter. I have spent the morning testing this and it seems to work in all instances.

If you have time, I'd love to understand why you took the approach you did. I was muddling about with INDEX, MATCH and MAX functions, trying to find the row number that met the conditions of Product and "X" and then find the MAX value in that row, which would be the most recent date.

 

[Peter_SSs]

If you have time, I'd love to understand why you took the approach you did.

Why is a difficult question to answer. Experience I guess.
The INDEX/AGGREGATE structure picks out the row that matches the product and "X".
LOOKUP a very big number (9^9) in that row can't find the very big number so returns the last number that it can find in the row. That is the right hand date in the row.

I was muddling about with INDEX, MATCH and MAX functions,

I don't know much about your data but MAX may not get the last date. For example, MAX in this row would return 400,000 not 24/06/2023

Yamezz.xlsm
	A	B	C								K	L	M	N	O	P	Q	R	S	T	U	V	W	X
1	Product	Batch	Current								Number	Date	Number2	Date3	Number4	Date5	Number6	Date7	Number8	Date9	Number10	Date11	Number12	Date13
2	Apple	14	X								1608	6/03/2018	400,000	30/06/2022	294	31/08/2022	257	26/10/2022	97	5/05/2023	54	24/06/2023
Sheet1