Cómo buscar valores múltiples en Excel: paso a paso

Cuando se trabaja con grandes conjuntos de datos en Microsoft Excel, puede resultar complicado encontrar varios valores que coincidan con varias condiciones. Algunas funciones integradas, por ejemplo, BUSCARV, se diseñaron originalmente para trabajar con valores únicos.

Se pueden combinar varias funciones de Excel para buscar múltiples valores. Incluyen funciones BUSCARV, ÍNDICE, COINCIDIR y SI. Las versiones actuales de Excel pueden usar matrices dinámicas, mientras que las versiones anteriores usan fórmulas de matriz.

Este artículo le mostrará exactamente cómo usar estas funciones en fórmulas que encuentran múltiples valores en sus datos.

¡Vamos!

Tabla de contenido

• Cómo usar BUSCARV con valores múltiples
  • ¿Qué son las fórmulas de matriz?
  • Ejemplo de BUSCARV
  • Últimas versiones de Excel
  • Versiones anteriores
  • Coincidencia exacta vs. Coincidencia aproximada
  • Más sobre BUSCARV
• Cómo usar INDEX y MATCH para buscar valores múltiples
  • Cómo usar INDEX y MATCH juntos en Excel 365
  • Trabajar con versiones anteriores de Excel
• Cómo usar la función IF para encontrar valores múltiples
  • Versiones Actuales de Excel
  • Versiones anteriores de Excel
• Cómo usar SUMPRODUCT para múltiples criterios
• 3 errores comunes con las funciones de búsqueda
  • 1. Errores #N/A
  • 2. #REF! errores
  • 3. Errores circulares
• Cómo usar IFERROR con funciones de búsqueda
• 7 técnicas de búsqueda avanzada
  • 1. Búsqueda de posición relativa
  • 2. Función PEQUEÑA
  • 3. Función FILA
  • 4. Función FILTRO
  • 5. Columnas auxiliares
  • 6. Matrices dinámicas
  • 7. Consulta de poder
• Pensamientos finales

Cómo usar BUSCARV con valores múltiples

La función BUSCARV a menudo se usa para encontrar valores únicos en un rango de datos. Sin embargo, también puede buscar varias coincidencias en Excel con esta fórmula de búsqueda.

De forma predeterminada, solo devolverá el primer valor coincidente que encuentre. Sin embargo, puede modificar la función para que devuelva varios valores mediante una fórmula de matriz.

¿Qué son las fórmulas de matriz?

Una fórmula de matriz es una fórmula que puede realizar cálculos en matrices de datos. Se llama fórmula de matriz porque puede devolver una matriz de resultados, en lugar de un solo valor.

Hay varios pasos sencillos al crear una fórmula de matriz:

1. Seleccione un rango de celdas para buscar.

2. Introduzca la fórmula en la barra de fórmulas.

3. Presione Ctrl + Shift + Enter para completarlo.

La sintaxis de una fórmula de matriz es similar a la de una fórmula regular, pero incluye llaves {} alrededor de la fórmula. Las llaves indican que la fórmula es una fórmula de matriz y que devolverá una matriz de valores.

Los ejemplos de este artículo le mostrarán cómo usar fórmulas de matriz correctamente.

Ejemplo de BUSCARV

Nuestro ejemplo tiene cinco elementos en la columna A de una hoja de trabajo:

• Manzana

• Banana

• Pan

• Zanahoria

• Cereza

La tarea es verificar si hay tres frutas específicas en esta lista: manzana, plátano y cereza.

Últimas versiones de Excel

La sintaxis de BUSCARV depende de la versión de Excel que esté utilizando.

Las versiones más recientes ( Excel 365 o Excel 2021 ) admiten matrices dinámicas. Esta función permite que las fórmulas devuelvan múltiples resultados que se "desbordan" en celdas adyacentes.

Esta es la sintaxis (el signo igual inicia la fórmula):

=BUSCARV(valor_buscado, matriz_tabla, núm_índice_col, [búsqueda_rango])

• lookup_value: El valor que desea buscar.

• table_array: toda la tabla de datos que desea buscar.

• col_index_num: el número de columna de la tabla en table_array que contiene los datos que desea devolver.

• range_lookup: Opcional. Especifica si desea una coincidencia exacta o una coincidencia aproximada.

Nuestro ejemplo específico utiliza esta fórmula:

=BUSCARV({“manzana”,”plátano”,”cereza”},A1:A5,1,FALSO)

Cuando se ingresa la fórmula en la celda B1, los resultados se derraman en las celdas C1 y D1. Esta imagen muestra el ejemplo en acción:

Versiones anteriores

Si está utilizando una versión anterior de Excel que no admite matrices dinámicas (por ejemplo, Excel 2019 o anterior), deberá utilizar un enfoque ligeramente diferente con una fórmula de matriz.

Sigue estos pasos:

1. Haga clic en la celda en la que desea mostrar los resultados del primer elemento (p. ej., la columna B).

2. Escriba la siguiente fórmula sin presionar Intro todavía: =BUSCARV({“manzana”,”plátano”,”cereza”}, A1:A5, 1, FALSO)

3. Presione Ctrl + Shift + Enter para convertir esto en una fórmula de matriz.

4. Copie la celda B1 y péguela en la celda de abajo (o use los controladores de relleno).

Cuando usa Ctrl + Shift + Enter, Excel agrega llaves alrededor de la fórmula. Esto indica que es una fórmula matricial.

Coincidencia exacta vs. Coincidencia aproximada

De forma predeterminada, la función BUSCARV utiliza una coincidencia aproximada. Esto significa que devolverá la coincidencia más cercana que pueda encontrar, incluso si los valores de las celdas no son una coincidencia exacta.

Si desea realizar una coincidencia exacta, puede establecer el argumento range_lookup en FALSO.

Tenga en cuenta que las coincidencias aproximadas funcionan mejor con valores numéricos ordenados. Por lo general, no es apropiado cuando el valor de la celda es texto.

Más sobre BUSCARV

Si desea obtener más información sobre esta función versátil, consulte estos artículos:

• cómo comparar dos columnas en Excel con BUSCARV

• cómo encontrar discrepancias con BUSCARV

Ahora que está configurado con la función BUSCARV, echemos un vistazo a otras dos funciones que pueden hacer lo que hace de una manera diferente: ÍNDICE y COINCIDIR.

Cómo usar INDEX y MATCH para buscar valores múltiples

Puede combinar las funciones ÍNDICE y COINCIDIR para encontrar varios valores en varias filas.

La función ÍNDICE en Excel devuelve un valor o referencia a una celda dentro de un rango específico.

=ÍNDICE(matriz, núm_fila, [num_columna])

• matriz: el rango de celdas en las que se buscará el valor.

• row_num: el número de fila dentro de la matriz desde la que se devolverá un valor.

• column_num: (opcional) el número de columna dentro de la matriz desde la que devolver un valor. Si se omite, la función devuelve la fila completa.

La función COINCIDIR en Excel devuelve la posición de un valor dentro de un rango específico.

=COINCIDIR(valor_buscado, matriz_buscada, [tipo_coincidencia])

• lookup_value: el valor que se buscará dentro de lookup_array.

• lookup_array: el rango de celdas en las que se buscará el valor_buscado.

• match_type: (Opcional) El tipo de coincidencia a realizar. Si se omite, la función realiza una coincidencia exacta.

Cómo usar INDEX y MATCH juntos en Excel 365

Para usar ÍNDICE y COINCIDIR juntos para buscar múltiples valores en Excel, debe usar una fórmula de matriz.

Trabajando con los datos de muestra anteriores, esta es la fórmula en Excel 365:

=ÍNDICE(A1:A5, COINCIDIR({“manzana”,”plátano”,”cereza”}, A1:A5, 0))

El ejemplo anterior se descompone así:

• ÍNDICE: esto devuelve el valor de una celda en un rango específico basado en un número de fila y columna dado. En este caso, devolverá el valor del rango A1:A5.

• A1:A5: este es el rango de tabla definido donde está buscando el valor y desde el cual se devolverá el resultado.

• COINCIDIR: busca un elemento específico en un rango de celdas y devuelve la posición relativa de ese elemento en el rango.

• {“manzana”,”plátano”,”cereza”}: esta es la constante de matriz que contiene los valores que desea buscar.

• A1:A5: este es el rango donde MATCH buscará los valores de la constante de matriz.

• 0: este es el tipo de coincidencia para la función MATCH. En este caso, es 0, lo que significa que está buscando una coincidencia exacta en lugar de una coincidencia cercana.

 Esta imagen muestra la fórmula en acción:

Trabajar con versiones anteriores de Excel

Si está utilizando un archivo de Excel anterior que no admite matrices dinámicas (por ejemplo, Excel 2019 o anterior), deberá utilizar un enfoque diferente.

Debido a que las versiones anteriores no admiten las fórmulas que se "derraman" en celdas adyacentes, deberá dividir el uso en tres fórmulas separadas.

Sigue estos pasos:

1. Haga clic en la celda donde desea obtener el resultado del primer elemento (p. ej., celda B1)

2. Escriba la siguiente fórmula:

3. =ÍNDICE(A1:A5, COINCIDIR(“manzana”, A1:A5, 0))

4. Presiona Enter para ejecutar la fórmula.

5. Escriba esta fórmula en la celda B2: = ÍNDICE (A1: A5, COINCIDIR ("plátano", A1: A5, 0))

6. Escriba esta fórmula en la celda B3: = ÍNDICE (A1: A5, COINCIDIR ("cereza", A1: A5, 0))

Esta imagen muestra la referencia de celda B3:

Las funciones ÍNDICE y COINCIDIR no son las únicas que pueden permitirle encontrar múltiples valores. En la siguiente sección, veremos cómo puede usar la función IF como alternativa.

Cómo usar la función IF para encontrar valores múltiples

Otra forma de buscar valores de celdas múltiples según ciertos criterios es usar la función IF con otras funciones.

La función IF le permite probar múltiples condiciones y devolver diferentes resultados según el resultado de esas pruebas.

Por ejemplo, supongamos que tiene una tabla de datos de ventas con columnas para Producto y Ventas. Desea buscar y sumar el monto de las ventas de dos de los tres productos.

Versiones Actuales de Excel

Para encontrar la suma de la columna Ventas donde el producto es "Apple" o "Banana" usando la función IF, puede usar una fórmula de matriz con las funciones IF, SUM y OR.

Suponiendo que sus datos comiencen en la celda A1, use la siguiente fórmula:

=SUMA(SI((A2:A4=”Apple”)+(A2:A4=”Banana”), B2:B4, 0))

La sección (A2:A4=”Apple”)+(A2:A4=”Banana”) crea una matriz que tiene un valor de 1 si la celda en el rango A2:A4 contiene “Apple” o “Banana”, y 0 en caso contrario .

La declaración IF verifica cada elemento del argumento de la matriz. Si el valor es 1 (es decir, el producto es “Apple” o “Banana”), toma el valor correspondiente en la columna Ventas (rango B2:B4); de lo contrario, toma 0.

La función SUMA suma los valores de la función SI, sumando efectivamente los valores de ventas tanto para "Apple" como para "Banana".

Esta imagen muestra la fórmula en acción en el rango de búsqueda:

Versiones anteriores de Excel

En Excel 2019 o anterior, debe usar una fórmula de matriz. Sigue estos pasos:

1. Escriba la fórmula pero no presione Enter.

2. Presione Ctrl + Shift + Enter para convertirlo en una fórmula de matriz.

Excel agregará corchetes {} alrededor de la fórmula, lo que indica que es una fórmula de matriz.

A continuación, veremos cómo podría usar SUMPRODUCT para buscar varios valores según sus criterios. ¡Vamos!

Cómo usar SUMPRODUCT para múltiples criterios

La función SUMPRODUCT también le permite buscar múltiples valores basados ​​en múltiples criterios.

Debido a que no requiere el uso de una fórmula de matriz, la sintaxis es la misma independientemente de la versión de Excel.

Usando los mismos datos que en el ejemplo anterior, la fórmula se ve así:

=SUMAPRODUCTO((A2:A4=”Apple”)+(A2:A4=”Banana”), B2:B4)

La sección (A2:A4=”Apple”)+(A2:A4=”Banana”) crea una matriz que tiene un valor de 1 si la celda en el rango A2:A4 contiene “Apple” o “Banana”, y 0 en caso contrario .

La función SUMAPRODUCTO multiplica los elementos de la matriz con los elementos correspondientes en la columna Ventas (rango B2:B4). Luego suma los valores resultantes, sumando efectivamente los valores de ventas tanto para "Apple" como para "Banana".

La siguiente fórmula lo muestra en acción:

Las funciones de Excel son increíbles cuando funcionan como se espera, pero a veces puede encontrar errores. En la siguiente sección, cubrimos algunos de los errores comunes y cómo puede solucionarlos.

3 errores comunes con las funciones de búsqueda

Las funciones de búsqueda a veces pueden devolver errores que pueden ser frustrantes y llevar mucho tiempo para solucionarlos. Los tres errores más comunes que encontrará son:

1. #N/A Errores

2. #¡ÁRBITRO! errores

3. Errores circulares

1. Errores #N/A

El error #N/A ocurre cuando el valor de búsqueda no se puede encontrar en la matriz de búsqueda.

Hay varias razones por las que puede ocurrir este error, incluyendo:

• el valor de búsqueda está mal escrito o es incorrecto.

• la matriz de búsqueda no está ordenada en orden ascendente.

• el valor de búsqueda no está en el conjunto de datos.

Cuando el valor de búsqueda no está en el conjunto de datos, esta es información útil. Sin embargo, los usuarios de Excel sin experiencia pueden pensar que #N/A significa que algo salió mal con la fórmula. La siguiente sección muestra cómo hacer que esto sea más fácil de usar.

2. #REF! errores

¡La referencia! se produce un error cuando la matriz de búsqueda o la matriz de retorno se eliminan o mueven.

Este error se puede solucionar actualizando las referencias de celda en la función de búsqueda.

3. Errores circulares

A medida que combina funciones en fórmulas complejas, Excel puede decirle que tiene una referencia circular.

Le resultará más fácil investigarlos si usa nuestra guía para encontrar referencias circulares en Excel .

Cómo usar IFERROR con funciones de búsqueda

La función IFERROR es una herramienta útil para manejar errores en las funciones de búsqueda. Le permite especificar un valor o fórmula para devolver si la función de búsqueda devuelve un error.

La sintaxis de la función IFERROR es la siguiente:

=SI.ERROR(valor, valor_si_error)

· valor: valor o fórmula que se quiere evaluar.

· Valor_si_error: el valor o fórmula a devolver si el primer argumento devuelve un error.

Por ejemplo, supongamos que tiene una función BUSCARV que busca varios valores en una tabla. En la imagen a continuación, uno de los valores no existe en el rango de datos buscado.

Como puede ver, se muestra el error #N/A, que puede resultar confuso para los usuarios de Excel sin experiencia.

En su lugar, puede usar IFERROR para mostrar una celda en blanco o un mensaje que dice "No encontrado" con esta sintaxis:

=SI.ERROR(BUSCARV(valor_buscado, matriz_tabla, índice_columna, FALSO), “No encontrado”)

En este ejemplo, si la función BUSCARV devuelve un error, la función IFERROR devolverá el mensaje "No encontrado" en su lugar.

Esta imagen muestra la fórmula en acción. La columna B tiene el valor faltante, mientras que la columna C y la columna D han encontrado coincidencias.

Hemos cubierto mucho terreno hasta ahora y finalmente está listo para aprender técnicas más avanzadas para búsquedas, que es el tema de la siguiente sección.

7 técnicas de búsqueda avanzada

Buscar múltiples valores en Excel puede ser una tarea desafiante, especialmente cuando se trata de grandes conjuntos de datos. Puede encontrar problemas de rendimiento con un procesamiento lento.

Hay siete técnicas de búsqueda avanzada que puede utilizar para que el proceso sea más fácil y eficiente.

• Búsqueda de posición relativa

• Función PEQUEÑA

• Función FILA

• Función FILTRO

• Columnas auxiliares

• Matrices dinámicas

• consulta de poder

1. Búsqueda de posición relativa

Una de las formas más sencillas de buscar varios valores en Excel es mediante la búsqueda de posición relativa. Esto implica especificar los desplazamientos de fila y columna de una celda dada para ubicar el valor deseado.

Por ejemplo, si tiene una tabla de datos y desea buscar un valor que está dos filas hacia abajo y tres columnas a la derecha de una celda determinada, puede usar la siguiente fórmula:

=DESPLAZAMIENTO(celda, 2, 3)

2. Función PEQUEÑA

Otra técnica útil para la búsqueda de valores múltiples es usar la función PEQUEÑA. Esta función devuelve el enésimo valor más pequeño en un rango de celdas.

Al combinar esta función con otras funciones de búsqueda como ÍNDICE y COINCIDIR, puede buscar múltiples valores según criterios específicos.

Por ejemplo, la siguiente fórmula busca el segundo valor más pequeño en un rango de celdas que cumplen una determinada condición:

=ÍNDICE(datos, COINCIDIR(PEQUEÑO(SI(criterios, rango), 2), rango, 0))

3. Función FILA

La función ROW también se puede usar para búsquedas de valores múltiples en Excel. Esta función devuelve el número de fila de una celda determinada, que se puede usar para hacer referencia a celdas en una tabla de datos.

Por ejemplo, la siguiente fórmula busca un valor en una tabla en función de un identificador único:

=ÍNDICE(datos, COINCIDIR(identificador_único, datos[identificador_único], 0), número_índice_columna)

4. Función FILTRO

La función FILTRO no está disponible en versiones anteriores de Excel.

En Excel 365, puede usarlo para filtrar un rango de celdas según ciertos criterios y devolver solo los valores que cumplen esos criterios. Esta es la sintaxis y tres argumentos:

=FILTRO(matriz, incluir, [si_vacío])

• matriz: los datos específicos que desea filtrar.

• include: el argumento include es el criterio o las condiciones que desea aplicar a la matriz.

• [if_empty] (opcional): el valor que se devolverá si ninguna fila o columna cumple los criterios especificados en el argumento de inclusión.

Por ejemplo, la siguiente fórmula funciona con los datos de ejemplo para encontrar coincidencias para dos de los tres elementos en la primera columna y sumar sus valores correspondientes en la segunda columna.

=SUMA(FILTRO(B2:B4, (A2:A4=”Apple”)+(A2:A4=”Banana”)))

Esta imagen muestra cuántas filas coinciden y la suma de los diferentes valores:

5. Columnas auxiliares

Puede usar una columna auxiliar para unir varios campos dentro de una función como BUSCARV.

Suponga que está trabajando con nombres y apellidos en una hoja separada. Puede concatenarlos en una columna auxiliar a la que se hace referencia en la fórmula final.

6. Matrices dinámicas

Como aprendió en ejemplos anteriores, los usuarios de Microsoft 365 pueden aprovechar las matrices dinámicas para la búsqueda de varios valores en Excel.

Las matrices dinámicas le permiten devolver múltiples valores de una sola fórmula, lo que facilita la búsqueda de grandes cantidades de datos.

7. Consulta de poder

Power Query es una poderosa herramienta en Excel que se puede usar para devolver valores basados ​​en múltiples criterios.

Por ejemplo, este video encuentra datos desordenados en una hoja de cálculo y los limpia.

Pensamientos finales

¡Eso concluye nuestra inmersión profunda en el arte de buscar múltiples valores en Excel! Una vez que haya dominado las fórmulas BUSCARV, ÍNDICE, COINCIDIR y matriz, se encontrará pasando rápidamente a través de conjuntos de datos complejos como un cuchillo caliente a través de la mantequilla.

La clave es entender la sintaxis y la lógica detrás de cada fórmula. Tenga en cuenta que estas fórmulas pueden ser complejas, por lo que es importante tomarse su tiempo y probarlas minuciosamente antes de confiar en ellas para análisis de datos importantes.

Excel es una herramienta poderosa, pero solo es tan buena como el usuario. Así que siga perfeccionando esas habilidades, siga experimentando y pronto será el maestro de las búsquedas de valores múltiples. Hasta la próxima, ¡siga procesando esos números y haciendo que Excel haga el trabajo duro por usted!