¿Podemos obtener múltiples valores de búsqueda para un elemento en una sola celda (separados por coma o espacio)?
Muchos de mis colegas me han hecho esta pregunta varias veces. Y después de muchos golpes de cabeza, me di cuenta de que es extremadamente difícil (si no imposible) hacer esto usando las funciones de Excel.
Así que hice un poco de codificación VBA para crear una función personalizada (también llamada función definida por el usuario) en Excel.
En este tutorial, le mostraré cómo obtener múltiples valores de búsqueda en una sola celda (con y sin repetición).
Glosario
Obtener múltiples valores de búsqueda en una sola célula (con repetición)
Para obtener múltiples valores de búsqueda en una sola celda, necesitamos crear una función en VBA (similar a la función VLOOKUP) que compruebe cada celda de una columna y si se encuentra el valor de búsqueda, lo añada al resultado.
Aquí está el código VBA que puede hacer esto:
«Código de Sumit Bansal» (https://trumpexcel.com) Función SingleCellExtract (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Resultado tenue como cuerda Para i = 1 Para LookupRange.Columnas(1).Recuento.de.células Si LookupRange.Cells(i, 1) = Lookupvalue entonces Resultado = Resultado & » » & LookupRange.Cells(i, ColumnNumber) & «,» Finalizar si Siguiente… SingleCellExtract = Left(Result, Len(Result) – 1) Fin de la función
¿Dónde poner este código?
- Abra un libro de trabajo y haga clic en Alt + F11 (esto abre la ventana del editor de VBA).
- En esta ventana del Editor de VBA, a la izquierda, hay un explorador de proyectos (donde se listan todos los libros y hojas de trabajo). Haz clic con el botón derecho del ratón en cualquier objeto del libro de trabajo donde quieras que funcione este código, y ve a Insertar —–; Módulo.
- En la ventana del módulo (que aparecerá a la derecha), copie y pegue el código anterior.
- Ahora ya está todo listo. Ve a cualquier celda del libro de trabajo y escribe =SingleCellExtract y conecta los argumentos de entrada necesarios (es decir, LookupValue, LookupRange, ColumnNumber).
¿Cómo funciona esta fórmula?
Esta función funciona de manera similar a la función VERDADERO.
Se necesitan 3 argumentos como entradas:
1.Lookupvalue – Una cadena que necesitamos buscar en un rango de células. 2.LookupRange – Una serie de celdas de donde necesitamos buscar los datos ($B3:$C18 en este caso). 3.ColumnNumber- Es el número de columna de la tabla/matriz de la que se debe devolver el valor de coincidencia (2 en este caso).
Cuando utiliza esta fórmula, comprueba cada celda de la columna de la izquierda en la gama de búsqueda y cuando encuentra una coincidencia, añade el resultado en la celda en la que ha utilizado la fórmula.
Recuerda: Guarda el libro de trabajo como un libro de trabajo habilitado para macros (.xlsm o .xls) para reutilizar esta fórmula de nuevo. Además, esta función estaría disponible sólo en este libro de trabajo y no en todos los libros de trabajo.
Pruébalo tú mismo… Descarga el archivo desde aquí
Aprende a automatizar tareas repetitivas aburridas con VBA en Excel. Únete al curso de VBA en Excel
Obtener múltiples valores de búsqueda en una sola célula (sin repetición)
Existe la posibilidad de que haya repeticiones en los datos.
Si utiliza el código usado arriba, le dará repeticiones en el resultado también.
Si quieres obtener el resultado donde no hay repeticiones, necesitas modificar el código un poco.
Aquí está el código VBA que le dará múltiples valores de búsqueda en una sola celda sin ninguna repetición.
«Código de Sumit Bansal» (https://trumpexcel.com) Función MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Resultado tenue como cuerda Para i = 1 Para LookupRange.Columnas(1).Recuento.de.células Si LookupRange.Cells(i, 1) = Lookupvalue entonces Para J = 1 Para i – 1 Si LookupRange.Cells(J, 1) = Lookupvalue entonces Si LookupRange.Cells(J, ColumnNumber) = LookupRange.Cells(i, ColumnNumber) Entonces Ir a Saltar Finalizar si Finalizar si Siguiente J Resultado = Resultado & » » & LookupRange.Cells(i, ColumnNumber) & «,» Saltar: Finalizar si Siguiente… MultipleLookupNoRept = Left(Result, Len(Result) – 1) Fin de la función
Una vez que hayas colocado este código en el VB Editor (como se muestra arriba en el tutorial), podrás usar la función MultipleLookupNoRept .
Aquí tienes una instantánea del resultado que obtendrás con esta función MultipleLookupNoRept .
Inténtalo tú mismo… Descarga el archivo de ejemplo
También te pueden gustar los siguientes tutoriales de Excel:
- Cómo crear y utilizar un complemento de Excel.
- Cómo ejecutar una macro en Excel.
- Encuentra la última ocurrencia de un valor de búsqueda en una lista de Excel
- Cómo usar VLOOKUP con criterios múltiples
- Cómo grabar una macro en Excel – Una guía paso a paso.
- <svg version="1.1" height="32" viewBox="0 0 23