Drop-down menu entered semi-automatic excel 2019 is it possible?

[AZOUTE]

Hello everyone,

Could I have help: I want to make a drop-down menu with semi-automatic entry but I am blocked by the FILTER function that unfortunately does not exist in Excel 2019 (I am on MAC and using the Excel 2019 version).
I would like to know if by using formulas, we can create an equivalent the FILTER function
Thanks in advance
AZOUTE

Classeur.xlsx
	E
2	#NAME?
Feuil1

Cell Formulas
Range	Formula
E2	E2	=FILTER(A2:A24,ISNUMBER(SEARCH(C2,A2:A24)),"Pas de résultat")

Cells with Data Validation
Cell	Allow	Criteria
D2	List	=_xlfn.ANCHORARRAY($E$2)
E2	Any value

 

[AZOUTE]

Hi,
A screenshot of the project carried out with Excel online

Thanks
AZOUTE

 

[AZOUTE]

Hi everyone

Please
It seems that no one is interested in my question,
I just want to transform this formula into a formula that works in Excel 2019
=FILTER(A2:A24,ISNUMBER(SEARCH(C2,A2:A24)),"No result")

 

[Peter_SSs]

I just want to transform this formula into a formula that works in Excel 2019
=FILTER(A2:A24,ISNUMBER(SEARCH(C2,A2:A24)),"No result")

In that case try this in E2, copied down

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$24)/ISNUMBER(SEARCH(C$2,A$2:A$24)),ROWS(E$2:E2))),"")

 

[AZOUTE]

In that case try this in E2, copied down

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$24)/ISNUMBER(SEARCH(C$2,A$2:A$24)),ROWS(E$2:E2))),"")

Hi Peter_SSs,

Thank you for answering.
I tried your formula but that's what it gives me as an error

Menuderoulsaisisemiauto.xlsx
	E
2	#NAME?
Feuil1

Cell Formulas
Range	Formula
E2	E2	=IFERROR(INDEX(A,AGGREGATE(15,6,ROW(A$2:A$17)/ISNUMBER(SEARCH(C$2,A$2:A$17)),ROWS(E$2:E2))),"")

 

[Peter_SSs]

I tried your formula

That isn't the formula that I suggested.

 

[AZOUTE]

Sorry for the mistake earlier...

I have the impression that the formula does nothing
It doesn't react...

Menuderoulsaisisemiauto.xlsx
	E
2
Feuil1

Cell Formulas
Range	Formula
E2	E2	=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(A$2:A$24)/ISNUMBER(SEARCH(C$2,A$2:A$24)),ROWS(E$2:E2))),"")

 

[Peter_SSs]

When using XL2BB you need to select the range that you want to show before you click Mini Sheet

In your language shouldn't it be AGREGAT not AGGREGATE?

 

[AZOUTE]

Hi, Peter_SSs

My Excel version is in French, I adapted your formula like this : =SIERREUR(INDEX(tbl_Clients[#Tout];AGREGAT(15;6;LIGNE(tbl_Clients[Clients])/ESTNUM(CHERCHE(C$2;tbl_Clients[Clients]));LIGNES(E$2:E2)));"")
I got what I was looking for, your help was very precious to me. A big thank you.
If I can afford a question? Why the formula was not written automatically in cells E3, E4, E5...etc: I would like it to be dynamic

Thanks again for the help you gave me

AZOUTE

Menuderoulsaisisemiauto.xlsx
	E
2	Daouda GUEYE
Feuil1

Cell Formulas
Range	Formula
E2	E2	=SIERREUR(INDEX(tbl_Clients[#Tout];AGREGAT(15;6;LIGNE(tbl_Clients[Clients])/ESTNUM(CHERCHE(C$2;tbl_Clients[Clients]));LIGNES(E$2:E2)));"")

 

[AZOUTE]

When using XL2BB you need to select the range that you want to show before you click Mini

OK Thank for this info

Menuderoulsaisisemiauto.xlsx
	A	B	C	D	E	F	G
1	Clients
2	Daouda GUEYE				Daouda GUEYE
3	Serge LAFFONT
4	Patrick MORRAIN
5	Astou KOULIBALY
6	Pierre DUFFIN
7	Yve BERNARD
8	Binta LY
9	Anta BARRY
10	Christophe DRUND
11	André BESTRAN
12	Félix DURHINOND
13	Donacien ALMERD
14	Khoudia DIALLO
15	George BRUNDEL
16	Moussa BEYE
17	Alphonse TOURE
18	Oulimata DIAW
19	Astou DIAW
20	Astou FALL
21	Binta TOURE
22	Anta SALL
23	Bruno MENDES
24	Celestin MAURE
25	Braham TALL
Feuil1

Cell Formulas
Range	Formula
E2	E2	=SIERREUR(INDEX(tbl_Clients[#Tout];AGREGAT(15;6;LIGNE(tbl_Clients[Clients])/ESTNUM(CHERCHE(C$2;tbl_Clients[Clients]));LIGNES(E$2:E2)));"")

Cells with Data Validation
Cell	Allow	Criteria
D2	List	=$E$2:$E$30