How to extract data with PARTIAL TEXT CRITERIA based on the NAMES

[GerryZ]

Hi all
I've a big data base (COLMNS A B C ) an from this data i need to extract all the records with NAME CRITERIA but I can use only a part of the critiria
PARTIAL NAME (PARTIAL TEXT)
thank you very much everybody!

	A	B	C	D	E	F	G
1	DATA	NOME	TOTALE
2	03/08/2014	Elisa	€ 49,00		CRITERIO
3	24/08/2014	Serena	€ 40,00
4	14/09/2014	Alice	€ 38,00
5	05/10/2014	Giorgia	€ 28,00		DATA	NOME	TOTALE
6	26/10/2014	Ilaria	€ 38,00
7	16/11/2014	Luisa	€ 43,00
8	07/12/2014	Giulia	€ 47,00
9	28/12/2014	Alessandra	€ 27,00
10	18/01/2015	Federico	€ 38,00
11	08/02/2015	Andrea	€ 34,00
12	01/03/2015	Elisa	€ 27,00
13	22/03/2015	Giada	€ 44,00
14	12/04/2015	Alessandra	€ 48,00
15	03/05/2015	Martina	€ 41,00
16	24/05/2015	Alessandro	€ 48,00
17	14/06/2015	Francesca	€ 29,00
18	05/07/2015	Lucia	€ 28,00
19	26/07/2015	Elisa	€ 28,00
20	16/08/2015	Davide	€ 54,00
21	06/09/2015	Alessia	€ 24,00
22	27/09/2015	Elisa	€ 46,00

<COLGROUP><COL style="WIDTH: 30px"><COL style="WIDTH: 76px"><COL style="WIDTH: 72px"><COL style="WIDTH: 56px"><COL style="WIDTH: 24px"><COL style="WIDTH: 78px"><COL style="WIDTH: 66px"><COL style="WIDTH: 67px"></COLGROUP><TBODY>
</TBODY>

Excel tables to the web >> Excel Jeanie HTML 4

 

[Aladin Akyurek]

Would you provide an example CRITERIO?

 

[GerryZ]

Example criteria GIUL or ALES and extract correspondent data
Thank you!

 

[Aladin Akyurek]

Example criteria GIUL or ALES and extract correspondent data
Thank you!

DATA	NOME	TOTALE
2014-08-03	Elisa	€ 49,00		CRITERIO
2014-08-24	Serena	€ 40,00		ALES
2014-09-14	Alice	€ 38,00
2014-10-05	Giorgia	€ 28,00		DATA	NOME	TOTALE	IDX
2014-10-26	Ilaria	€ 38,00		2014-12-28	Alessandra	€ 27,00	8
2014-11-16	Luisa	€ 43,00		2015-04-12	Alessandra	€ 48,00	13
2014-12-07	Giulia	€ 47,00		2015-05-24	Alessandro	€ 48,00	15
2014-12-28	Alessandra	€ 27,00		2015-09-06	Alessia	€ 24,00	20
2015-01-18	Federico	€ 38,00
2015-02-08	Andrea	€ 34,00
2015-03-01	Elisa	€ 27,00
2015-03-22	Giada	€ 44,00
2015-04-12	Alessandra	€ 48,00
2015-05-03	Martina	€ 41,00
2015-05-24	Alessandro	€ 48,00
2015-06-14	Francesca	€ 29,00
2015-07-05	Lucia	€ 28,00
2015-07-26	Elisa	€ 28,00
2015-08-16	Davide	€ 54,00
2015-09-06	Alessia	€ 24,00
2015-09-27	Elisa	€ 46,00

<TBODY>
</TBODY>

E3: ALES

E6, enter and copy across to G6, and down:

=IF($H6="","",INDEX($A$2:$C$22,$H6,MATCH(E$5,$A$1:$C$1,0)))

H6, just enter and copy down:

=IFERROR(AGGREGATE(15,6,(ROW($B$2:$B$22)-ROW($B$2)+1)/
  ISNUMBER(SEARCH($E$3,$B$2:$B$22)),ROWS($H$6:H6)),"")

 

[GerryZ]

Woooooooowww!!! Aladin it works fine. I like because I don't have to make CT+SHIFT+ENTER But I want ask you if is possible doing it without helper column? Thank you very much!!

 

[Aladin Akyurek]

Woooooooowww!!! Aladin it works fine. I like because I don't have to make CT+SHIFT+ENTER But I want ask you if is possible doing it without helper column? Thank you very much!!

You should try to revise your position on the array formulas and on dismissing the idx column. The latter provides for more speed. That said, try:

E6, copied to H6 and down:

=IFERROR(INDEX(A$2:A$22,AGGREGATE(15,6,(ROW($B$2:$B$22)-ROW($B$2)+1)/
  ISNUMBER(SEARCH($E$3,$B$2:$B$22)),ROWS(E$6:E6))),"")

 

[GerryZ]

@ Aladin I prefer this formula

Thank you very helpfully!!!

 

[Aladin Akyurek]

@ Aladin I prefer this formula

Thank you very helpfully!!!

No problem. You are welcome.