Drop-down list

%3CLINGO-SUB%20id%3D%22lingo-sub-1921443%22%20slang%3D%22en-US%22%3EDrop-down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1921443%22%20slang%3D%22en-US%22%3EHi%2C%20I%20don't%20know%20how%20I%20can%20create%20a%20Drop-down%20list%20that%20change%20when%20I%20write%20some%20letters%3CBR%20%2F%3EI%20need%20this%20things%20because%20I%20have%20a%20list%20of%20more%20than%20800%20foods%20in%20a%20table%20and%20in%20another%20cell%20I%20have%20to%20put%20one%20that%20is%20in%20this%20list.%20This%20list%20is%20too%20long%20to%20search%20every%20time%2C%20so%20I%20want%20to%20write%20some%20letters%20and%20than%20I%20want%20to%20open%20the%20list%20but%20only%20with%20the%20foods%20with%20those%20letters%3CBR%20%2F%3EFor%20example%20if%20I%20want%20to%20find%20%22peach%22%20i%20write%20%22pea%22%20an%20then%20I%20want%20to%20open%20the%20list%20with%20food%20like%20%22peach%22%20and%20%22pear%22%20not%20somethings%20with%20out%20letters%20%22pea%22%20like%20bacon%3CBR%20%2F%3EI%20aslo%20explain%20in%20Italian%20if%20there%20is%20anyone%20who%20unterstands%20it%3CBR%20%2F%3EIn%20alcune%20celle%20ho%20bisogno%20di%20creare%20un%20elenco%20a%20tendina%20che%20comprenda%20una%20lista%20di%20800%20alimenti%2C%20io%20vorrei%20poter%20scrivere%20alcune%20lettere%20e%20poi%20aprire%20la%20lista%20e%20scegliere%2C%20per%C3%B2%20a%20quel%20punto%20vorrei%20una%20lista%20limitata%20agli%20alimenti%20che%20contengono%20quelle%20lettere%2C%20invece%20con%20la%20convalida%20dati%20che%20ho%20messo%20per%20ora%20se%20apro%20la%20lista%20me%20li%20da%20sempre%20tutti%20e%20800%20quindi%20%C3%A8%20inutile%3CBR%20%2F%3EIo%20non%20posso%20scrivere%20l'alimento%20per%20intero%20perch%C3%A9%20devos%20scriverli%20in%20modo%20preciso%20visto%20che%20ci%20sono%20delle%20formule%20che%20si%20basano%20su%20questo%20alimenti%20che%20inserisco%20e%20non%20so%20come%20sono%20scritti%20tutti%20e%20800%20gli%20alimenti%20in%20modo%20preciso%3CBR%20%2F%3E%3CBR%20%2F%3EGrazie%20mille%3CBR%20%2F%3E%3CBR%20%2F%3EThanks%20a%20lot%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1921443%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1921465%22%20slang%3D%22en-US%22%3ERe%3A%20Drop-down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1921465%22%20slang%3D%22en-US%22%3E%3CP%3EHi%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F877727%22%20target%3D%22_blank%22%3E%40AlessandroModena%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20check%20the%20below%20blog%20post%20%26amp%3B%20video%20link%20will%20be%20help%20for%20you%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.xelplus.com%2Fsearchable-drop-down-list%2F%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3ESearchable%20Drop%20Down%20List%20in%20Excel%20(Very%20Easy%20with%20Dynamic%20Arrays)%20-%20Xelplus%20-%20Leila%20Gharani%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%3ERegards%2C%20Faraz%20Shaikh%20%7C%20MCT%2C%20MIE%2C%20MOS%20Master%2C%20Excel%20Expert%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CEM%3E%3CFONT%20color%3D%22%23808080%22%3EIf%20you%20find%20the%20above%20solution%20resolved%20your%20query%20don't%20forget%20mark%20as%26nbsp%3B%3CSPAN%3EOfficial%2FBest%20Answer%20%26amp%3B%20like%20it%20to%20help%20the%20other%20members%20find%20it%20more.%3C%2FSPAN%3E%3C%2FFONT%3E%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1960466%22%20slang%3D%22en-US%22%3ERe%3A%20Drop-down%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1960466%22%20slang%3D%22en-US%22%3ESorry%20I%20see%20the%20answer%20only%20today%2C%20I%20thought%20I%20would%20receive%20an%20email%20in%20case%20of%20a%20reply%3CBR%20%2F%3EThanks%20a%20lot%2C%20this%20things%20is%20really%20helpful%2C%20but%20I%20have%20a%20problem%2C%20in%20this%20way%20i%20can%20search%20only%20in%20one%20cell%2C%20the%20one%20that%20the%20formula%20referes%20to%3CBR%20%2F%3EI%20need%20to%20search%20in%20a%20lot%20of%20cell%20because%20my%20goal%20is%20to%20create%20a%20diet%20with%20this%20excel%20file%2C%20so%20I%20have%20a%20database%20with%20all%20the%20food%20and%20the%20composition%20and%20than%20I%20have%20some%20tables%20for%20each%20day%20of%20diet%20in%20every%20day.%3CBR%20%2F%3EI%20can't%20search%20every%20time%20in%20the%20same%20celle%20because%20I%20need%20to%20add%20every%20food%20comoposition%20to%20the%20total.%3CBR%20%2F%3EDo%20you%20know%20how%20I%20can%20resove%20this%20things%3F%3CBR%20%2F%3EI%20tried%20with%20the%20combo%20box%20but%20it%20is%20too%20hard%20for%20me%2C%20I%20don't%20know%20how%20I%20can%20do%20it%3CBR%20%2F%3EI%20atached%20the%20file%3CBR%20%2F%3EI%20put%20your%20formula%20in%20the%20red%20cell%20(dieda!D4)%2C%20but%20i%20need%20to%20searche%20in%20all%20the%20yellow%20cell%20because%20with%20the%20name%20of%20the%20food%20and%20the%20grams%20I%20can%20have%20the%20composition%20with%20the%20formula%20index%2Fmatch%3CBR%20%2F%3EThanks%20a%20lot!%3CBR%20%2F%3ESincerely%3CBR%20%2F%3EAlessandro%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor
Hi, I don't know how I can create a Drop-down list that change when I write some letters
I need this things because I have a list of more than 800 foods in a table and in another cell I have to put one that is in this list. This list is too long to search every time, so I want to write some letters and than I want to open the list but only with the foods with those letters
For example if I want to find "peach" i write "pea" an then I want to open the list with food like "peach" and "pear" not somethings with out letters "pea" like bacon
I aslo explain in Italian if there is anyone who unterstands it
In alcune celle ho bisogno di creare un elenco a tendina che comprenda una lista di 800 alimenti, io vorrei poter scrivere alcune lettere e poi aprire la lista e scegliere, però a quel punto vorrei una lista limitata agli alimenti che contengono quelle lettere, invece con la convalida dati che ho messo per ora se apro la lista me li da sempre tutti e 800 quindi è inutile
Io non posso scrivere l'alimento per intero perché devos scriverli in modo preciso visto che ci sono delle formule che si basano su questo alimenti che inserisco e non so come sono scritti tutti e 800 gli alimenti in modo preciso

Grazie mille

Thanks a lot
2 Replies

Hi @AlessandroModena 

 

Please check the below blog post & video link will be help for you 

 

Searchable Drop Down List in Excel (Very Easy with Dynamic Arrays) - Xelplus - Leila Gharani

 

Regards, Faraz Shaikh | MCT, MIE, MOS Master, Excel Expert

If you find the above solution resolved your query don't forget mark as Official/Best Answer & like it to help the other members find it more.

Sorry I see the answer only today, I thought I would receive an email in case of a reply
Thanks a lot, this things is really helpful, but I have a problem, in this way i can search only in one cell, the one that the formula referes to
I need to search in a lot of cell because my goal is to create a diet with this excel file, so I have a database with all the food and the composition and than I have some tables for each day of diet in every day.
I can't search every time in the same celle because I need to add every food comoposition to the total.
Do you know how I can resove this things?
I tried with the combo box but it is too hard for me, I don't know how I can do it
I atached the file
I put your formula in the red cell (dieda!D4), but i need to searche in all the yellow cell because with the name of the food and the grams I can have the composition with the formula index/match
Thanks a lot!
Sincerely
Alessandro