Extracting Records With Partial Search

[NickFru]

Hi, I have a some data and just wanted to know the syntax of operation in order to extract a partial name search together with dates. On the attached below, I have done a count on =COUNTIFS(Date Range),">="&(Start Date),(Date Range),"<="&(End Date), First Name Range, "*"&First Name Extract"*".

I then am using the formula, =IF(ROWS(F5:F5)>Count, "", INDEX(DATE RANGE(AGGREGATE(15,6,(ROW(DATE RANGE)-ROW(A2)+1)/ISNUMBER(SEARCH(FIRST NAME RANGE,F2)+(DATE RANGE>=G2)+(DATE RANGE<=H2),ROWS(F5:F5)). When I put this formula in, I get a VALUE error.

Would anyone be able to assist to correct. I have attached.

Date	First Name	Group
01/07/2024​	Nick	A
08/07/2024​	Dave	B
15/07/2024​	Helen	C
22/07/2024​	Paul	A
29/07/2024​	Tom	B
05/08/2024​	James	C
12/08/2024​	Alex	B
19/08/2024​	Carolyn	A
26/08/2024​	Nick	C
02/09/2024​	Alex	A
09/09/2024​	James	B
16/09/2024​	Helen	C
23/09/2024​	Tom	A
30/09/2024​	Paul	B

First Name	Start Date	End Date	Count
Ni	01/07/2024​	26/08/2024​	2​

Date	First Name	Group
#VALUE!​

Thanks

Nick

























Nick

 

[Fluff]

Hi & welcome to MrExcel.
How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I
1	Date	First Name	Group			First Name	Start Date	End Date	Count
2	01/07/2024	Nick	A			Ni	01/07/2024	26/08/2024	2
3	08/07/2024	Dave	B
4	15/07/2024	Helen	C
5	22/07/2024	Paul	A			01/07/2024	Nick	A
6	29/07/2024	Tom	B			26/08/2024	Nick	C
7	05/08/2024	James	C
8	12/08/2024	Alex	B
9	19/08/2024	Carolyn	A
10	26/08/2024	Nick	C
11	02/09/2024	Alex	A
12	09/09/2024	James	B
13	16/09/2024	Helen	C
14	23/09/2024	Tom	A
15	30/09/2024	Paul	B
Sheet5

Cell Formulas
Range	Formula
F5:H8	F5	=IF(ROWS(F$5:F5)>$I$2, "", INDEX(A$2:A$15,AGGREGATE(15,6,(ROW($A$2:$A$15)-ROW($A$2)+1)/(ISNUMBER(SEARCH($F$2,$B$2:$B$15)))/($A$2:$A$15>=$G$2)/($A$2:$A$15<=$H$2),ROWS(F$5:F5))))

 

[NickFru]

That's brilliant, thanks ever so much Fluff

 

[Fluff]

You're welcome & thanks for the feedback.