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 Excel VLOOKUP 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 el puntaje de matemáticas de cada estudiante para los niveles de examen respectivos podría ser un desafío.
Se puede argumentar que una mejor opción sería reestructurar el conjunto de datos o utilizar una tabla dinámica. Si eso funciona para usted, nada de eso. Pero en muchos casos, está atascado con los datos que tiene y es posible que la tabla dinámica no sea una opción.
En tales casos, este tutorial es para ti.
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 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 sucede en la hoja de trabajo.
- Lo hace más rápido en comparación con las funciones de matriz (notable en grandes conjuntos de datos).
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:
- Inserte una columna auxiliar entre las columnas B y C.
- Utilice la siguiente fórmula en la columna de ayuda: = A2 & ”|” & B2
- Esto crearía calificadores únicos para cada instancia, como se muestra a continuación.
- Esto crearía calificadores únicos para cada instancia, como se muestra a continuación.
- Utilice la siguiente fórmula en G3 = VLOOKUP ($ F3 & ”|” & G $ 2, $ C $ 2: $ D $ 19,2,0)
- Copiar para todas las celdas.
¿Como funciona esto?
Creamos calificadores únicos para cada instancia de un nombre y el examen. En la función BUSCARV utilizada aquí, el valor de búsqueda se modificó a $ F3 & ”|” & G $ 2 para que ambos criterios de búsqueda se combinen y se utilicen 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 usa para obtener la puntuación de C2: D19.
Aclaraciones:
Hay un par de preguntas que probablemente se le ocurran, así que pensé que intentaré responderlas aquí:
- ¿Por qué he usado | símbolo al unir los dos criterios? - En algunas condiciones excepcionalmente raras (pero posibles), puede tener dos criterios que son diferentes pero 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 si bien 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 utilizar ninguna columna auxiliar mientras utilizan 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: uso de la función ELEGIR
El uso de fórmulas de matriz en lugar de columnas auxiliares 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.
Teniendo en cuenta el mismo conjunto de datos que se utilizó anteriormente, aquí está la fórmula que le dará el resultado:
= VLOOKUP ($ E3 & ”|” & F $ 2, CHOOSE ({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.
¿Como funciona esto?
La fórmula también utiliza el concepto de columna auxiliar. La diferencia es que, en lugar de colocar la columna de ayuda en la hoja de trabajo, considérela como datos de ayuda virtual que forman parte de la fórmula.
Déjame mostrarte 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 daría la fórmula ELEGIR.
El resultado es {"Matt | Prueba unitaria", 91; "Bob | Prueba unitaria", 52;…}
Es una matriz en la que una coma representa la siguiente celda de 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 tiene la puntuación.
Ahora, cuando usa 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.
Descargar el archivo de ejemplo
También puede utilizar otras fórmulas para realizar una búsqueda con varios criterios (como ÍNDICE / COINCIDIR o SUMPRODUCTO).
¿Hay alguna otra forma que conozcas de hacer esto? Si es así, compártelo conmigo en la sección de comentarios.
También le pueden gustar los siguientes tutoriales de BÚSQUEDA:
- VLOOKUP vs. ÍNDICE / PARTIDO
- Obtenga múltiples valores de búsqueda sin repetición en una sola celda.
- Cómo hacer que BUSCARV sea sensible a mayúsculas y minúsculas.
- Utilice IFERROR con BUSCARV para deshacerse de los errores # N / A.
