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

 

[Peter_SSs]

I removed [#Headers] in the formula to eliminate the returned error.

Of course removing part of a formula to eliminate an error is more than likely to introduce another error - as it did by not returning the correct results.

I think it is just that you needed to translate #Headers (since it is not an actual column heading) into French. Try this instead

=SI(C$2="";"";SIERREUR(INDEX(tblClients[Clients];AGREGAT(15;6;(LIGNE(tblClients[Clients])-LIGNE(tblClients[#En-têtes]))/ESTNUM(CHERCHE(C$2;tblClients[Clients]));LIGNE(INDIRECT("1:"&NB.SI(tblClients[Clients];"*"&C$2&"*")))));""))

Edit: Oops, I think your table name is actually tbl_Clients

 

[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)));"")