Consejo: Otros idiomas son traducidos por Google. Puedes visitar el English versión de este enlace.
Iniciar sesión
x
or
x
x
Suscríbete
x

or

¿Cómo crear una lista desplegable de búsqueda en Excel?

Para una lista desplegable con numerosos valores, encontrar uno adecuado no es un trabajo fácil. Anteriormente, hemos introducido un método para completar automáticamente la lista desplegable al ingresar la primera letra en el cuadro desplegable. Además de la función de autocompletar, también puede hacer que la lista desplegable pueda buscarse para mejorar la eficiencia de trabajo al encontrar los valores adecuados en la lista desplegable. Para hacer búsquedas en la lista desplegable, haga lo siguiente, paso a paso, muestra el tutorial.

Crear una lista desplegable de búsqueda en Excel


Busque (encuentre y reemplace) fácilmente los textos en todos los libros abiertos o en ciertas hojas de trabajo:

Haz click Kutools > Navegación > Buscar y reemplazar para buscar rápidamente (buscar y reemplazar) textos o valores en todos los libros abiertos o en ciertas hojas de trabajo en Excel. ¡Descargue la función completa 60-day gratis de Kutools for Excel ahora!

Kutools for Excel: con más de 200 útiles complementos de Excel, de prueba sin límite en días 60. Descargue la versión de prueba gratuita Ahora!

Ficha Office Habilite la edición y navegación con pestañas en Office y haga su trabajo mucho más fácil ...
Kutools for Excel trae las características avanzadas de 300 a Excel y aumenta su productividad en 80%
  • Super Formula Bar (edite fácilmente varias líneas de texto y fórmula); Diseño de lectura (lee y edita fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas y mantener datos; Contenido de celdas divididas; Combinar filas duplicadas y suma / promedio... Prevenir células duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Celdas múltiples sin cambiar la referencia de fórmula; Crear referencias automáticamente a múltiples hojas; Insertar viñetas, Casillas de verificación y más ...
  • Fórmulas favoritas e insertadas rápidamente, Gamas, cuadros y cuadros; Cifrar celdas con contraseña Crear una lista de correo y enviar correos electrónicos ...
  • Extracto del texto, Agregar texto, Eliminar por posición, Eliminar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtro a otras hojas); Clasificación avanzada por mes / semana / día, frecuencia y más; Filtro especial por negrita, cursiva ...
  • Combinar libros de trabajo y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes xls, xlsx y PDF...
  • Kutools funciona con Office 2007-2019 y 365. Es compatible con todos los idiomas y es muy fácil de instalar o implementar. Funciones completas de prueba gratuita de 60-day.

Crear una lista desplegable de búsqueda en Excel


Por ejemplo, los datos de origen que necesita para la lista desplegable se encuentran en el rango A2: A9.

Este método requiere el cuadro combinado en lugar de la lista desplegable de validación de datos. Para crear una lista desplegable de búsqueda, haga lo siguiente.

1. Si no puedes encontrar el Desarrollador pestaña en la cinta, habilite la pestaña Desarrollador de la siguiente manera.

1). En Excel 2010 y 2013, haga clic en Envíe el > Opciones. Y en la Opciones cuadro de diálogo, haga clic en Personalizar cinta de opciones en el panel derecho, revisa Desarrollador caja, luego haga clic en OK botón. Ver captura de pantalla:

2). En Outlook 2007, haga clic en Oficina botón> Opciones de Excel. En la Opciones de Excel cuadro de diálogo, haga clic en MAS POPULARES en la barra derecha, luego verifica Mostrar pestaña Desarrollador en la Cinta cuadro, y finalmente haga clic en el OK botón.

2. Después de mostrar el Desarrollador pestaña, por favor haga clic Desarrollador > recuadro > Caja combo. Ver captura de pantalla:

3. Dibuje el cuadro Combo en la hoja de trabajo y haga clic con el botón derecho. Seleccionar Propiedades desde el menú contextual.

4. En el Propiedades cuadro de diálogo, necesita:

1). Seleccionar Falso en el objeto AutoWordSelect campo;

2). Especifique una celda en el LinkedCell campo. En este caso, ingresamos A12;

3). Seleccionar 2-fmMatchEntryNone en el objeto MatchEntry campo;

4). Tipo La lista desplegable en el ListFillRange campo;

5). Cierra el Propiedades caja de diálogo. Ver captura de pantalla:

5. Ahora cierre el modo de diseño haciendo clic Desarrollador > Modo de diseño.

6. Seleccione una celda en blanco C2 y luego copie y pegue la fórmula = - ISNUMBER (IFERROR (BÚSQUEDA ($ A $ 12, A2,1), "")) en la barra de fórmulas, y presiona la tecla Enter. Lo arrastran hacia la celda C9 para completar automáticamente las celdas seleccionadas con la misma fórmula. Ver captura de pantalla:

Notas:

1. El $ A $ 12 es la celda que ha especificado en el campo LinkedCell en el paso 4;

2. Después de finalizar el paso anterior, ahora puede probarlo. Ingrese una letra C en el cuadro desplegable, verá que todas las celdas que contienen C están llenas con el número 1.

7. Seleccione la celda D2, ponga la fórmula = IF (C2 = 1, COUNTIF ($ C $ 2: C2,1), "") en la barra de fórmulas, y presiona la tecla Enter. A continuación, arrastre el controlador de relleno en el D2 hasta D9 para completar el rango D3: D9.

8. Seleccionar celda E2, copiar y pegar fórmula =IFERROR(INDEX($A$2:$A$9,MATCH(ROWS($D$2:D2),$D$2:$D$9,0)),"") en la barra de fórmulas y presiona la tecla Enter. A continuación, arrastre el controlador de relleno en E2 hacia abajo hasta E9 para llenar las celdas. Luego verá que las celdas están llenas como se muestra en la siguiente captura de pantalla.

9. Ahora necesita crear un rango de nombre. Por favor haz click Fórmula > Definir nombre.

10. En el Nuevo nombre cuadro de diálogo, escriba La lista desplegable dentro Nombre caja, tipo fórmula =$E$2:INDEX($E$2:$E$9,MAX($D$2:$D$9),1) en el Se refiere a caja, luego haga clic en OK botón.

11. Ahora habilite el modo de diseño haciendo clic Desarrollador > Modo de diseño. Luego haga doble clic en el cuadro Combo que creó en el paso 3 para abrir el Microsoft Visual Basic para aplicaciones ventana.

12. Copie y pegue el siguiente código de VBA en el editor de código.

Código de VBA: haga que la lista desplegable se pueda buscar

Private Sub ComboBox1_GotFocus()
	ComboBox1.ListFillRange = "DropDownList"
	Me.ComboBox1.DropDown
End Sub

13. Cierra el Microsoft Visual Basic para aplicaciones ventana.

A partir de ahora, cuando empiece a escribir en el cuadro de lista, comenzará una búsqueda ambigua y solo mostrará los valores relevantes en la lista desplegable.

Nota: Después de cerrar y volver a abrir la hoja de cálculo, el código de VBA que ha creado en el paso 12 se elimina automáticamente. Por lo tanto, debe guardar este libro de trabajo como formato de cuaderno de Excel habilitado para macros.


Office Tab - Navegación con pestañas, edición y administración de libros en Excel:

Office Tab trae la interfaz con pestañas como se ve en los navegadores web como Google Chrome, nuevas versiones de Internet Explorer y Firefox a Microsoft Excel. Será una herramienta de ahorro de tiempo e irremplazable en su trabajo. Vea la demostración a continuación:

¡Haz clic para obtener una versión de prueba gratuita de Office Tab!

Pestaña Office para Excel


Artículos relacionados:


Kutools for Excel: la mejor herramienta de productividad de Office aumenta su productividad en un 80%

  • Super Formula Bar (edite fácilmente varias líneas de texto y fórmula); Diseño de lectura (lee y edita fácilmente un gran número de celdas); Pegar en rango filtrado...
  • Combinar celdas / filas / columnas y mantener datos; Contenido de celdas divididas; Combinar filas duplicadas y suma / promedio... Prevenir células duplicadas; Comparar rangos...
  • Seleccione Duplicado o Único Filas; Seleccionar filas en blanco (todas las celdas están vacías); Super Find y Fuzzy Find en muchos libros de trabajo; Selección aleatoria ...
  • Copia exacta Celdas múltiples sin cambiar la referencia de fórmula; Crear referencias automáticamente a múltiples hojas; Insertar viñetas, Casillas de verificación y más ...
  • Fórmulas favoritas e insertadas rápidamente, Gamas, cuadros y cuadros; Cifrar celdas con contraseña Crear una lista de correo y enviar correos electrónicos ...
  • Extracto del texto, Agregar texto, Eliminar por posición, Eliminar espacio; Crear e imprimir subtotales de paginación; Convertir entre contenido de celdas y comentarios...
  • Súper filtro (guardar y aplicar esquemas de filtro a otras hojas); Clasificación avanzada por mes / semana / día, frecuencia y más; Filtro especial por negrita, cursiva ...
  • Combinar libros de trabajo y hojas de trabajo; Combinar tablas basadas en columnas clave; Dividir datos en varias hojas; Conversión por lotes xls, xlsx y PDF...
  • Funciona con Office 2007-2019 y 365, y es compatible con todos los idiomas. Es fácil de implementar en su empresa. Funciones completas de prueba gratuita de 60-day.
pestaña kte 201905

Office Tab lleva la interfaz con pestañas a Office y hace que su trabajo sea mucho más fácil

  • Habilitar la edición y lectura con pestañas en Word, Excel, PowerPoint, Editor, Acceso, Visio y Proyecto.
  • Abra y cree varios documentos en nuevas pestañas de la misma ventana, en lugar de en nuevas ventanas.
  • ¡Aumenta tu productividad en un 50% y reduce cientos de clics de ratón por ti todos los días!
fondo officetab
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Sourav Singha · 16 days ago
    Sir How to use this in excel userform combobox....? plz help
    • To post as a guest, your comment is unpublished.
      crystal · 9 days ago
      Hi Sourav Singha,
      Can't use it in a userform combobox. Sorry for the inconvenience.
  • To post as a guest, your comment is unpublished.
    Josh · 2 months ago
    Is there a way to make it call up a hyperlink? My email is joshuarobertdaniels@gmail.com
  • To post as a guest, your comment is unpublished.
    Vrezh · 3 months ago
    I have a problem. My list is in Armenian language, and I see ??????-s instead of the letters. how can I fix this problem? Thank you in advance
    • To post as a guest, your comment is unpublished.
      crystal · 2 months ago
      Hi Vrezh,
      Sorry this kind of problem can't be solved yet. Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    Steve Olah · 6 months ago
    How can I use this? I have two problem
    1st I would like use ComboBox1 for a full column, so I have D column, it should see empty.
    When I click into a cell in D column example D7 or D8(etc) I should get a Combo in D7 or D8 etc cell and after select just see the result, not the combo too.

    But how can I add combobox dynamically to D2, D4, D11 etc when click or before.
    I need for I can search with typing too, so simple(not active-x) combo is wrong.

    2nd how set padding? - my combo text when I search is not see whole because itt has padding.

    3th if my source is C column, how drop empty elements from list
  • To post as a guest, your comment is unpublished.
    sigidapurnomo purnomo · 10 months ago
    I had try tutorial drodown list searchable, Some like that,. But i'am can't make searcable from list and Combo Box Search??? How to make VBA Macro Connected in Excel??
  • To post as a guest, your comment is unpublished.
    Mubashir · 11 months ago
    I want to make this drop down to work for whole column, so that with multiple entries, I have this search suggestion option available every time. Above option, just shows suggestion for one time. Please help
  • To post as a guest, your comment is unpublished.
    Min · 1 years ago
    Hi. I get many helps from your post. However, it doesn't make automatic dropdown if there are mixed language on the list (e.g: first cell is written in English, second cell is written in Korean etc.) Has anyone had solve this problem?
  • To post as a guest, your comment is unpublished.
    dan · 1 years ago
    The automatic dropdown list is not working. Everything else is working. Do you know where my snag might lie?
    • To post as a guest, your comment is unpublished.
      dan · 1 years ago
      I figured it my be with the last step. I put the VBA code in my personal.xlsb worksheet but looks like the code needs to be on the sheet of the respective workbook. hazah
  • To post as a guest, your comment is unpublished.
    michael cianci · 1 years ago
    I got this to work but for some reason excel crashes if i attempt to use the arrows to select things from the drop down. has anyone had this issue? is it even supposed to be possible?
    thanks
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Michael,
      The problem you mentioned does not appear in my case. Which Office version do you use?
      • To post as a guest, your comment is unpublished.
        yogi · 1 years ago
        Dear Crystal,

        I got the same problem as michael does, excel crashes every time i use down arrow in the drop box, i got excel 2010 on my laptop, which version do you use?
        • To post as a guest, your comment is unpublished.
          crystal · 1 years ago
          Hi yogi,
          The method has been successfully tested in Office 2010, 2013 as well as 2016. No idea for this problem. Sorry about that.
  • To post as a guest, your comment is unpublished.
    alluxxx · 1 years ago
    Is there a way to prioritize the location of a letter in a word? I used this method, but when I type in "A," for example, I get terms with "A" anywhere in the word. I would prefer if it started showing all the terms that begin with "A". Is this possible?
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      Sorry for reply so late. If you want to search values in drop-down list that begin with a certain character, please change the formula in column C to
      =--ISNUMBER(IFERROR(SEARCH($A$12,MID(A3,1,1),1),"")).
      Thank you for your comment.
  • To post as a guest, your comment is unpublished.
    KATHLEEN · 1 years ago
    I may have misunderstood how this function is supposed to work, but i can only get the combo box search to populate one cell, A12 (using the example from the tutorial). If i click into A13 to populate the next cell with a different value from the drop down, it just replaces what i have in A12 and does not populate A13. I need this search to apply to any cell in column A from A12 down. Have i done something incorrect or does this combo box search only allow a single cell in the workbook to be populated with the result? Will be grateful for any help with this.
  • To post as a guest, your comment is unpublished.
    Kathleen · 1 years ago
    I may have misunderstood how this function is supposed to work, but i can only get the combo box search to populate one cell, A12 (using the example from the tutorial). If i click into A13 to populate the next cell with a different value from the drop down, it just replaces what i have in A12 and does not populate A13. I need this search to apply to any cell in column A from A12 down. Have i done something incorrect or does this combo box search only allow a single cell in the workbook to be populated with the result? Will be grateful for any help with this.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good Day,
      This combo box search only allow a single cell in the workbook to be populated with the result.
      I'll try to find another method to solve your problem.
  • To post as a guest, your comment is unpublished.
    Ben Johnston · 1 years ago
    I feel dumb, but immediately after posting, I realized I probably hadn't added the 1 to DropDownList1 in the VBA, and sure enough that was the problem! Thanks anyway!
  • To post as a guest, your comment is unpublished.
    Ben Johnston · 1 years ago
    Hello, thanks for the tutorial! I'm having an issue where every time I type in the combo box, "DropDownList1" disappears from the "ListFillRange" property. So long as I don't type in the box, if I retype "DropDownList1" in the property, the box does show suggestions. I have looked everything over and could not find any errors. Is this a common problem, and is there a way to fix it? Thank you for your time!
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Ben,
      I am also comfusing about the disappearing of the "DripDownList" from the "ListFillRange" property
      But it does not influence the finally rsult of making the drop-down list seachable.
  • To post as a guest, your comment is unpublished.
    dave · 1 years ago
    is there a way to have the search box put the top result if left blank? in the case of this example it would automatically put china if it was left blank
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear dave,
      Would you please provide a screenshot of your spreadsheet showing what you are exactly trying to do?
  • To post as a guest, your comment is unpublished.
    Al B · 2 years ago
    I've had an ongoing issue with all documents I've used this method on. A shadow of the drop-down box reappears underneath it each time I click into another cell within the spreadsheet and begin typing. It's beyond just a nuisance because when the shadow drops down, it prevents use of any additional searchable drop-down boxes. Please help!!! This is affecting multiple documents we use throughout our organization.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Good day,
      Sorry for replying so late. The problem you methoded does not appear in my case.Would be nice if you could provide your Office verson. Thank you!
  • To post as a guest, your comment is unpublished.
    Gunawan Budianto · 2 years ago
    4. In the Properties dialog box, you need to:
    1). Select False in the AutoWordSelect field;
    2). Specify a cell in the LinkedCell field. In this case, we enter A12;

    Why A12? thank's
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      This cell is optionally selected which can help to finish the whole operation. You can choose any one as you need.
  • To post as a guest, your comment is unpublished.
    Jelbin · 2 years ago
    Hi As in forum,
    I need to have this searchable dropdown for columns 2 to 500. Please let me know how i can as the second combo replicates the same in first which i dont want
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Dear Jelbin,
      Can't handle this. Sorry about that.
  • To post as a guest, your comment is unpublished.
    Havocknox · 2 years ago
    Thank you for this breakdown to make the combo box searchable. I have even gotten three of them working on the same page. My problem I have run into is when I start typing in the search information and the info narrows down, if I hit the down arrow key to select the item in the list Excel crashes on me. Has anyone had this happen, and if so have you found a way to solve this issue.
    • To post as a guest, your comment is unpublished.
      crystal · 1 years ago
      Hi,
      The problemm you mentioned does not appear in my case. Would you please provide your Office version?
  • To post as a guest, your comment is unpublished.
    Heric · 2 years ago
    Hi,

    your guide is most helpful, but i still encounter one last problem.
    I am trying to do a simple invoice, and do the drop down for my customer name cell, must my customer listing be in the same worksheet as my invoice worksheet? Is is possible i have two worksheet, "invoice" & "customer name", and do the drop down list for customer name at "invoice" worksheet?

    Thank you
  • To post as a guest, your comment is unpublished.
    Jaydie · 2 years ago
    Thank you, I used above and it works perfectly....

    Until you have two combo boxes in one sheet.. When you want to type in the second combo box it highlights the text in the first combo box and does not want to search
    If I leave the first box blank, the second box works fine

    Please help
  • To post as a guest, your comment is unpublished.
    NAJMA · 2 years ago
    plz help me
    i cannt enter formula in formula bar
    when i paste this formula & paste this =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),""))
    give me error.type :(
  • To post as a guest, your comment is unpublished.
    Ashok · 2 years ago
    HI, How to do the same searchable program for contnious rwo , i tried and it is working one row only , i want to do the same for below row also for different name
  • To post as a guest, your comment is unpublished.
    Ahmed Shahin · 2 years ago
    Hi Herb,

    What if i created a drop down list from another work sheet? the formula " =--ISNUMBER(IFERROR(SEARCH($A$2,H2,1),""))" has wrong reference and when i edit it it doesn't allow to put the right cell. what do you suggest? thank you
  • To post as a guest, your comment is unpublished.
    Yesenia · 2 years ago
    I, like Cristina above, would also like to know how to make multiple combo boxes for one sheet. I tried but when I begin typing in the second combobox two things happen: 1. no drop down list appears, and 2. the simple act of typing in combobox2 activates the selection from my original combobox1 and highlights it in the drop down from combobox1. I checked to make sure all of my coding says combobox2 for combobox2 etc. for the other boxes but there is a disconnect that I can't figure out.
    • To post as a guest, your comment is unpublished.
      Jaydie · 2 years ago
      I have the exact same problem, have you managed a solution yet??
  • To post as a guest, your comment is unpublished.
    FAUZI · 3 years ago
    Thank You.. Very helpfull.. God Bless You
  • To post as a guest, your comment is unpublished.
    Maarten · 3 years ago
    Hi,

    I can't fill in 'DropDownList' in the 'ListFillRange'.... What's the catch? I don't understand the solution of imad.
    Thanks.
    • To post as a guest, your comment is unpublished.
      Herb123987 · 3 years ago
      [quote name="Maarten"]Hi,

      I can't fill in 'DropDownList' in the 'ListFillRange'.... What's the catch? I don't understand the solution of imad.
      Thanks.[/quote]

      I posted this answer above for IMAD and saw this posting down here for MAARTEN so I figured I'd post this for him too.

      I have seen this "how to make an autofill / auto suggest DDL / combo box" on a few different sites and they ALL want you to put "something" in the ListFillRange Properties field [b]BEFORE[/b] they have you [b]create a named range[/b] by clicking Formula > Define Name ....... and the [b]ListFillRange will always go blank in the Properties window[/b] UNTIL you define the name (Formula > Define Name)

      THAT is why i think IMAD, above and MAARTEN below (here) was having the problem - not 100% sure though.
      • To post as a guest, your comment is unpublished.
        Maarten · 3 years ago
        Hi there,

        Thanks a lot for your solution. I gave up already, but I'll try again.
    • To post as a guest, your comment is unpublished.
      Andone · 3 years ago
      try to put this=--ISNUMBER(IFERROR(SEARCH($A$12,$A$2,1),"")) instead =--ISNUMBER(IFERROR(SEARCH($A$12,A2,1),"")) in step 6
  • To post as a guest, your comment is unpublished.
    imad · 3 years ago
    So I Finally got it to work! I attached the linkedcell to a vlookup and got all the information pulling into a row. I was wondering if there could be any extension on the vba to actually filter the table as we type?
  • To post as a guest, your comment is unpublished.
    imad · 3 years ago
    Mine isn't working. My dropdownlist label was not working in the "properties" for the combobox. Everytime I entered it, it disappeared. So I used "test" instead. I adjusted the macro with the word test instead of dropdowmlist. Let me know if there is something else I can do? Search not working.
    • To post as a guest, your comment is unpublished.
      Herb123987 · 3 years ago
      [quote name="imad"]Mine isn't working. My dropdownlist label was not working in the "properties" for the combobox. Everytime I entered it, it disappeared. So I used "test" instead. I adjusted the macro with the word test instead of dropdowmlist. Let me know if there is something else I can do? Search not working.[/quote]

      I have seen this "how to make an autofill / auto suggest DDL / combo box" on a few different sites and they ALL want you to put "something" in the ListFillRange field BEFORE they have you create a name range by clicking Formula > Define Name and the ListFillRange will always go blank in the Properties window UNTIL you define the name (Formula > Define Name)

      THAT is why i think IMAD, above and MAARTEN below was having the problem - not 100% sure though.
  • To post as a guest, your comment is unpublished.
    MarkC · 3 years ago
    For some reason when I click a selection from the drop down list after typing a few characters the drop down main value becomes blank... any idea why this would happen and how to get it to stop?

    I have a command button that I want to click to then put the selection into the next available cell in a given range, but again the value blanks out when I click on it.
    • To post as a guest, your comment is unpublished.
      imad · 3 years ago
      I have the exact same problem. I did everything right but the dropdownlist label just goes blank everytime I press enter. If you figured it out, please do share!
  • To post as a guest, your comment is unpublished.
    Cristina · 4 years ago
    Excellent post. Could you please explain how do you copy the same drop down list to multiple cells. I want to create an expense report and I want to be able to select a different expense on each row from the same drop down list. Thank you.
  • To post as a guest, your comment is unpublished.
    Prastuti · 4 years ago
    very nicely explained. Loved it. Thank you !!