Ver video – Cómo usar la función VLOOKUP con múltiples criterios

La función BUSCARV de Excel, en su forma básica, puede buscar un valor de búsqueda y devolver el valor correspondiente de la fila especificada.

Pero a menudo es necesario utilizar la BUSCARV de Excel con varios criterios.

Cómo utilizar BUSCARV con varios criterios

Suponga que tiene datos con el nombre de los estudiantes, el tipo de examen y la puntuación de matemáticas (como se muestra a continuación):

Usar la función BUSCARV para obtener la puntuación de matemáticas de cada alumno para los niveles de examen respectivos podría ser un desafío.

Se puede discutir que una mejor opción sería reestructurar el conjunto de datos o usar una tabla dinámica. Si eso funciona para usted, nada de eso. Pero en muchos casos, está atascado con los datos que tiene y la tabla dinámica puede no ser una opción.

En tales casos, este tutorial es para usted.

Ahora hay dos formas de obtener el valor de búsqueda usando BUSCARV con varios criterios.

• Usando una columna auxiliar.
• Usando la función ELEGIR.

BUSCARV con varios criterios: uso de una columna auxiliar

Soy un fanático de las columnas auxiliares en Excel.

Encuentro dos ventajas significativas de usar columnas auxiliares sobre fórmulas de matriz:

• Facilita la comprensión de lo que está pasando en la hoja de trabajo.
• Lo hace más rápido en comparación con las funciones de matriz (notable en conjuntos de datos grandes).

Ahora, no me malinterpretes. No estoy en contra de las fórmulas de matriz. Me encantan las cosas increíbles que se pueden hacer con fórmulas de matriz. Es solo que los guardo para ocasiones especiales cuando todas las demás opciones no son de ayuda.

Volviendo a la pregunta en cuestión, la columna de ayuda es necesaria para crear un calificador único. Este calificador único se puede utilizar para buscar el valor correcto. Por ejemplo, hay tres Matt en los datos, pero solo hay una combinación de Matt y Unit Test o Matt y Mid-Term.

Estos son los pasos:

¿Cómo funciona?

Creamos calificadores únicos para cada instancia de un nombre y el examen. En la función BUSCARV que se usa aquí, el valor de búsqueda se modificó a $ F3 & ”|” & G $ 2 de modo que ambos criterios de búsqueda se combinan y se utilizan como un único valor de búsqueda. Por ejemplo, el valor de búsqueda para la función BUSCARV en G2 es Matt | Prueba unitaria. Ahora, este valor de búsqueda se utiliza para obtener la puntuación de C2: D19.

Aclaraciones:

Hay un par de preguntas que probablemente se le ocurran, así que pensé que intentaré responderla aquí:

• ¿Por qué he usado | símbolo al unir los dos criterios? – En algunas condiciones excepcionalmente raras (pero posibles), es posible que tenga dos criterios que son diferentes pero que terminan dando el mismo resultado cuando se combinan. Aquí hay un ejemplo muy simple (perdóname por mi falta de creatividad aquí):

Tenga en cuenta que mientras A2 y A3 son diferentes y B2 y B3 son diferentes, las combinaciones terminan siendo las mismas. Pero si usa un separador, incluso la combinación sería diferente (D2 y D3).

• ¿Por qué inserté la columna auxiliar entre la columna B y C y no en el extremo izquierdo? – No hay nada de malo en insertar la columna de ayuda en el extremo izquierdo. De hecho, si no quiere manipular los datos originales, ese debería ser el camino a seguir. Lo hice porque me hace usar menos celdas en la función BUSCARV. En lugar de tener 4 columnas en la matriz de la tabla, podría administrarme con solo 2 columnas. Pero ese soy solo yo.

Ahora no hay una talla única que se adapte a todos. Algunas personas pueden preferir no usar ninguna columna auxiliar mientras usan BUSCARV con varios criterios.

Así que aquí está el método de columna no auxiliar para usted.

Descargar el archivo de ejemplo

BUSCARV con varios criterios: usar la función ELEGIR

Usar fórmulas de matriz en lugar de ayuda columnas le ahorra espacio en la hoja de trabajo y el rendimiento puede ser igualmente bueno si se usa menos veces en un libro de trabajo.

Considerando el mismo conjunto de datos que se usó anteriormente, aquí está la fórmula que le dará el resultado :

= VLOOKUP ($ E3 & ”|” & F $ 2, ELEGIR ({1,2} , $ A $ 2: $ A $ 19 & ”|” & $ B $ 2: $ B $ 19, $ C $ 2: $ C $ 19 ), 2,0)

Dado que esta es una fórmula de matriz, úsela con Control + Shift + Enter, en lugar de solo Enter.

¿Cómo funciona esto?

La fórmula también utiliza el concepto de columna auxiliar. La diferencia es que en lugar de poner la columna de ayuda en la hoja de trabajo, considérelo como datos de ayuda virtual que son parte de la fórmula.

Permítame mostrarle lo que quiero decir con datos de ayuda virtual.

En la ilustración anterior, cuando selecciono la parte ELEGIR de la fórmula y presiono F9, muestra el resultado que ELEGIR fórmula daría.

El resultado es {«Matt | Prueba unitaria”, 91; ”Bob | Prueba unitaria”, 52; ……}

Es una matriz donde una coma representa la siguiente celda en la misma fila y el punto y coma representa que los siguientes datos están en la siguiente columna. Por lo tanto, esta fórmula crea 2 columnas de datos: una columna tiene el identificador único y la otra la puntuación.

Ahora , cuando utiliza la función BUSCARV, simplemente busca el valor en la primera columna (de estos datos de 2 columnas virtuales) y devuelve la puntuación correspondiente.

Descargue el Archivo de ejemplo

También puede utilizar otras fórmulas para realizar una búsqueda con varios criterios (como INDICE / MATCH o SUMPRODUCT).