Obtenga múltiples valores de búsqueda en una sola celda (con y sin repetición)

¿Podemos buscar y devolver varios valores en una celda en Excel (separados por coma o espacio)?

Muchos de mis colegas y lectores me han hecho esta pregunta varias veces.

Excel tiene algunas fórmulas de búsqueda sorprendentes, como VLOOKUP, INDEX / MATCH (y ahora XLOOKUP), pero ninguna de ellas ofrece una forma de devolver múltiples valores coincidentes. Todos estos funcionan identificando la primera coincidencia y devolviéndola.

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.

Actualizar: Después de que Excel lanzó matrices dinámicas y funciones asombrosas como UNIQUE y TEXTJOIN, ahora es posible usar una fórmula simple y devolver todos los valores coincidentes en una celda (cubierto en este tutorial).

En este tutorial, le mostraré cómo hacer esto (si está usando la última versión de Excel - Microsoft 365 con todas las funciones nuevas), así como una forma de hacerlo en caso de que esté usando versiones anteriores ( usando VBA).

¡Entonces empecemos!

Buscar y devolver múltiples valores en una celda (usando fórmula)

Si está usando Excel 2016 o versiones anteriores, vaya a la siguiente sección donde muestro cómo hacer esto usando VBA.

Con la suscripción a Microsoft 365, su Excel ahora tiene funciones y características mucho más poderosas que no estaban en versiones anteriores (como XLOOKUP, Dynamic Arrays, funciones UNIQUE / FILTER, etc.)

Entonces, si está usando Microsoft 365 (anteriormente conocido como Office 365), puede usar los métodos cubiertos en esta sección para buscar y devolver múltiples valores en una sola celda en Excel.

Y como verá, es una fórmula realmente simple.

A continuación, tengo un conjunto de datos donde tengo los nombres de las personas en la columna A y la capacitación que han tomado en la columna B.

Haga clic aquí para descargar el archivo de ejemplo y siga

Para cada persona, quiero saber qué formación ha completado. En la columna D, tengo la lista de nombres únicos (de la columna A), y quiero buscar y extraer rápidamente todo el entrenamiento que cada persona ha realizado y obtenerlos en un solo conjunto (separados por una coma).

A continuación se muestra la fórmula que hará esto:

= TEXTJOIN (",", VERDADERO, SI (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, ""))

Después de ingresar la fórmula en la celda E2, cópiela para todas las celdas donde desea obtener los resultados.

¿Cómo funciona esta fórmula?

Permítanme deconstruir esta fórmula y explicar cada parte de cómo se combina y nos da el resultado.

La prueba lógica en la fórmula SI (D2 = $ A $ 2: $ A $ 20) verifica si la celda de nombre D2 es la misma que la del rango A2: A20.

Pasa por cada celda en el rango A2: A20 y verifica si el nombre es el mismo en la celda D2 o no. si es el mismo nombre, devuelve VERDADERO, de lo contrario, devuelve FALSO.

Entonces, esta parte de la fórmula le dará una matriz como se muestra a continuación:

{VERDADERO; FALSO; FALSO; VERDADERO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO; FALSO}

Dado que solo queremos obtener el entrenamiento para Bob (el valor en la celda D2), necesitamos obtener todo el entrenamiento correspondiente para las celdas que devuelven VERDADERO en la matriz anterior.

Esto se hace fácilmente especificando [valor_si_verdadero] parte de la fórmula IF como el rango que tiene el entrenamiento. Esto asegura que si el nombre en la celda D2 coincide con el nombre en el rango A2: A20, la fórmula IF devolverá todo el entrenamiento que esa persona ha tomado.

Y siempre que la matriz devuelva un FALSO, hemos especificado el valor [valor_si_falso] como “” (en blanco), por lo que devuelve un espacio en blanco.

La parte SI de la fórmula devuelve la matriz como se muestra a continuación:

{“Excel”; ””; ””; ”PowerPoint”; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””; ””}

Donde tiene los nombres del entrenamiento que Bob ha tomado y espacios en blanco donde el nombre no era Bob.

Ahora, todo lo que tenemos que hacer es combinar estos nombres de entrenamiento (separados por una coma) y devolverlos en una celda.

Y eso se puede hacer fácilmente usando la nueva fórmula TEXTJOIN (disponible en Excel2021-2022 y Excel en Microsoft 365)

La fórmula TEXTJOIN toma tres argumentos:

• el delimitador, que es "," en nuestro ejemplo, ya que quiero que el entrenamiento esté separado por una coma y un espacio
• VERDADERO - que le dice a la fórmula TEXTJOIN que ignore las celdas vacías y solo combine las que no están vacías
• La fórmula If que devuelve el texto que debe combinarse

Si está utilizando Excel en Microsoft 365 que ya tiene matrices dinámicas, puede ingresar la fórmula anterior y presionar Enter. Y si está utilizando Excel2021-2022, debe ingresar la fórmula y mantener presionadas las teclas Control y Shift y luego presionar Enter

Haga clic aquí para descargar el archivo de ejemplo y siga

Obtenga múltiples valores de búsqueda en una sola celda (sin repetición)

Dado que la fórmula ÚNICA solo está disponible para Excel en Microsoft 365, no podrá utilizar este método en Excel2021-2022

En caso de que haya repeticiones en su conjunto de datos, como se muestra a continuación, debe cambiar un poco la fórmula para que solo obtenga una lista de valores únicos en una sola celda.

En el conjunto de datos anterior, algunas personas se han capacitado varias veces. Por ejemplo, Bob y Stan han tomado la capacitación de Excel dos veces y Betty ha tomado la capacitación de MS Word dos veces. Pero en nuestro resultado, no queremos que se repita un nombre de entrenamiento.

Puede utilizar la siguiente fórmula para hacer esto:

= TEXTJOIN (",", VERDADERO, ÚNICO (SI (D2 = $ A $ 2: $ A $ 20, $ B $ 2: $ B $ 20, "")))

La fórmula anterior funciona de la misma manera, con un pequeño cambio. hemos utilizado la fórmula SI dentro de la función ÚNICA para que, en caso de que haya repeticiones en el resultado de la fórmula, la función ÚNICA lo elimine.

Haga clic aquí para descargar el archivo de ejemplo

Busque y devuelva múltiples valores en una celda (usando VBA)

Si usa Excel 2016 o versiones anteriores, no tendrá acceso a la fórmula TEXTJOIN. Entonces, la mejor manera de buscar y obtener múltiples valores coincidentes en una sola celda es usando una fórmula personalizada que puede crear usando VBA.

Para obtener múltiples valores de búsqueda en una sola celda, necesitamos crear una función en VBA (similar a la función BUSCARV) que verifique cada celda en una columna y, si se encuentra el valor de búsqueda, lo agregue al resultado.

Aquí está el código VBA que puede hacer esto:

'Código por Sumit Bansal (https://trumpexcel.com) Función SingleCellExtract (Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer) Dim i As Long Dim Resultado como cadena para i = 1 Para LookupRange.Columns (1) .Cells .Count If LookupRange.Cells (i, 1) = Lookupvalue Then Result = Result & "" & LookupRange.Cells (i, ColumnNumber) & "," End If Next i SingleCellExtract = Left (Result, Len (Result) - 1) Función final

¿Dónde poner este código?

1. Abra un libro de trabajo y haga clic en Alt + F11 (esto abre la ventana del Editor de VBA).
2. En esta ventana del Editor de VBA, a la izquierda, hay un explorador de proyectos (donde se enumeran todos los libros y hojas de trabajo). Haga clic con el botón derecho en cualquier objeto del libro de trabajo donde desee que funcione este código y vaya a Insertar -> Módulo.
3. En la ventana del módulo (que aparecerá a la derecha), copie y pegue el código anterior.
4. Ahora ya está todo listo. Vaya a cualquier celda del libro de trabajo y escriba = Extracto de celda única y conecte 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 BUSCARV.

Toma 3 argumentos como entradas:

1. Valor de búsqueda - Una cadena que necesitamos buscar en un rango de celdas.
2. LookupRange - Una matriz de celdas desde donde necesitamos buscar los datos ($ B3: $ C18 en este caso).
3. ColumnNumber - Es el número de columna de la tabla / matriz desde la que se devolverá el valor coincidente (2 en este caso).

Cuando usa esta fórmula, verifica cada celda en la columna más a la izquierda en el rango de búsqueda y cuando encuentra una coincidencia, se suma al resultado en la celda en la que ha utilizado la fórmula.

Recordar: Guarde el libro de trabajo como un libro de trabajo habilitado para macros (.xlsm o .xls) para reutilizar esta fórmula nuevamente. Además, esta función estaría disponible solo en este libro de trabajo y no en todos los libros de trabajo.

Haga clic aquí para descargar el archivo de ejemplo

Aprenda a automatizar tareas aburridas y repetitivas con VBA en Excel. Disfruta el Curso de Excel VBA

Obtenga múltiples valores de búsqueda en una sola celda (sin repetición)

Existe la posibilidad de que tenga repeticiones en los datos.

Si usa el código usado anteriormente, también le dará repeticiones en el resultado.

Si desea obtener el resultado donde no hay repeticiones, debe modificar un poco el código.

Aquí está el código VBA que le dará múltiples valores de búsqueda en una sola celda sin repeticiones.

'Código por Sumit Bansal (https://trumpexcel.com) Función MultipleLookupNoRept (Valor de búsqueda como cadena, Rango de búsqueda como rango, Número de columna como entero) Dim i Como resultado de atenuación larga como cadena para i = 1 Para LookupRange.Columns (1) .Cells .Contar si LookupRange.Cells (i, 1) = Lookupvalue Then For J = 1 To i - 1 Si LookupRange.Cells (J, 1) = Lookupvalue Entonces si LookupRange.Cells (J, ColumnNumber) = LookupRange.Cells (i, ColumnNumber) Luego Ir a Omitir Fin si finaliza si Siguiente J Resultado = Resultado & "" & LookupRange.Cells (i, ColumnNumber) & "," Omitir: Finalizar si sigue i MultipleLookupNoRept = Izquierda (Resultado, Len (Resultado) - 1) Finalizar Función

Una vez que haya colocado este código en el VB Editor (como se muestra arriba en el tutorial), podrá usar el MultipleLookupNoRept función.

Aquí hay una instantánea del resultado que obtendrá con este MultipleLookupNoRept función.

Haga clic aquí para descargar el archivo de ejemplo

En este tutorial, cubrí cómo usar fórmulas y VBA en Excel para encontrar y devolver múltiples valores de búsqueda en una celda en Excel.

Si bien se puede hacer fácilmente con una fórmula simple si está usando Excel en la suscripción de Microsoft 365, si está usando versiones anteriores y no tiene acceso a funciones como TEXTJOIN, aún puede hacerlo usando VBA creando su propia función personalizada.