Assistance with array formula

[tigerzen]

Part of this formula gives me what I need ie the IF function, the wrong date is however returned. I suspect that the SMALL function is the issue because I want the 4th and the 10th record and I keep getting 1 only. Don't think I'm far off the actual solution and I'll add the IFERROR to remove the error notifications. Perhaps the FILTER function would be more appropriate, anyway looking for anything that works.

Lookups Match.xlsx
	A	B	C	D	E
1	Date	Products		Lookup	Date of transaction
2	Saturday, September 3, 2022	Lock and hasp		Sink	Saturday, September 3, 2022
3	Saturday, September 17, 2022	Padlock			#N/A
4	Wednesday, October 12, 2022	Plastic			#N/A
5	Wednesday, October 12, 2022	Sink
6	Saturday, October 15, 2022	Cleaning products
7	Tuesday, October 18, 2022	Cleaning products
8	Thursday, October 20, 2022	String
9	Tuesday, November 8, 2022	Roller blind, gloves
10	Sunday, November 20, 2022	Screening, snail poison
11	Wednesday, December 7, 2022	Construction adhesive, Sink
12	Saturday, December 10, 2022	Lights
13	Sunday, December 18, 2022	Key, plugs
Sheet16

Cell Formulas
Range	Formula
E2:E4	E2	=INDEX($A$2:$A$13,SMALL(IF(ISNUMBER(SEARCH($D$2,$B$2:$B$13)),ROW(B$2:$B2)-ROW($B$2)+1),ROWS(B$2:$B2)))

 

[Anonymous1378]

Your first ROW() function should refer to the whole array and be absolute. If you have access to FILTER() you may use a more compact formula.

Book12
	A	B	C	D	E	F	G	H
1	Date	Products		Lookup	Date of transaction		Lookup	Date of transaction
2	03-Sep-22	Lock and hasp		Sink	12-Oct-22		Sink	12-Oct-22
3	17-Sep-22	Padlock			07-Dec-22			07-Dec-22
4	12-Oct-22	Plastic
5	12-Oct-22	Sink
6	15-Oct-22	Cleaning products
7	18-Oct-22	Cleaning products
8	20-Oct-22	String
9	08-Nov-22	Roller blind, gloves
10	20-Nov-22	Screening, snail poison
11	07-Dec-22	Construction adhesive, Sink
12	10-Dec-22	Lights
13	18-Dec-22	Key, plugs
Sheet1

Cell Formulas
Range	Formula
H2:H3	H2	=FILTER(A2:A13,ISNUMBER(SEARCH(G2,B2:B13)))
E2:E6	E2	=IFERROR(INDEX($A$2:$A$13,SMALL(IF(ISNUMBER(SEARCH($D$2,$B$2:$B$13)),ROW(B$2:$B$13)-ROW($B$2)+1),ROWS(B$2:$B2))),"")
Dynamic array formulas.

 

[tigerzen]

Thank you Anonymous1378! That FILTER is a wonderful function.