BUSCARV es una de las funciones más utilizadas en Excel. Busca un valor en un rango y devuelve un valor correspondiente en un número de columna especificado.
Ahora me encontré con un problema en el que tenía que buscar la fila completa y devolver los valores en todas las columnas de esa fila (en lugar de devolver un solo valor).
Así que esto es lo que tenía que hacer. En el siguiente conjunto de datos, tenía los nombres de los Representantes de ventas y las Ventas que hicieron en 4 trimestres en 2012. Tenía un menú desplegable con sus nombres y quería extraer las ventas máximas para ese Representante de ventas en esos cuatro trimestres.
Podría pensar en 2 formas diferentes de hacer esto: usando INDEX o VLOOKUP.
Buscar toda la fila / columna usando la fórmula INDICE
Aquí está la fórmula que creé para hacer esto usando Index
= GRANDE (INDICE ($ B $ 4: $ F $ 13, COINCIDIR (H3, $ B $ 4: $ B $ 13,0), 0), 1)
Cómo funciona:
Veamos primero la función ÍNDICE que está envuelta dentro de la función GRANDE.
= ÍNDICE ($ C $ 4: $ F $ 13, PARTIDA (H3, $ B $ 4: $ B $ 13,0), 0)
Analicemos de cerca los argumentos de la función INDICE:
- Matriz - $ B $ 4: $ F $ 1
- Número de fila - MATCH (H3, $ B $ 4: $ B $ 13,0)
- Número de columna - 0
Observe que he usado el número de columna como 0.
El truco aquí es que cuando usa el número de columna como 0, devuelve todos los valores en todas las columnas. Entonces, si selecciono John en el menú desplegable, la fórmula del índice devolvería los 4 valores de ventas para John {91064,71690,67574,25427}.
Ahora puedo usar la función Grande para extraer el valor más grande
Consejo profesional: use el número de columna / fila como 0 en la fórmula de índice para devolver todos los valores en columnas / filas.
Buscar toda la fila / columna usando la fórmula VLOOKUP
Si bien la fórmula de Index es ordenada, limpia y robusta, la forma de BUSCARV es un poco compleja. También acaba volviendo la función volátil. Sin embargo, hay un truco asombroso que compartiría en esta sección. Aquí está la fórmula:
= GRANDE (VLOOKUP (H3, B4: F13, ROW (INDIRECT ("2:" & COUNTA ($ B $ 4: $ F $ 4))), FALSE), 1)
Cómo funciona
- ROW (INDIRECT (“2:” & COUNTA ($ B $ 4: $ F $ 4))): esta fórmula devuelve una matriz {2; 3; 4; 5}. Tenga en cuenta que, dado que usa INDIRECTO, esto hace que esta fórmula sea volátil.
- VLOOKUP (H3, B4: F13, ROW (INDIRECT (“2:” & COUNTA ($ B $ 4: $ F $ 4))), FALSE) - Aquí está la mejor parte. Cuando los pone juntos, se convierte en BUSCARV (H3, B4: F13, {2; 3; 4; 5}, FALSO). Ahora observe que en lugar de un solo número de columna, le he dado una matriz de números de columna. Y BUSCARV busca obedientemente valores en todas estas columnas y devuelve una matriz.
- Ahora solo use la función GRANDE para extraer el valor más grande.
Recuerde usar Control + Shift + Enter para usar esta fórmula.
Consejo profesional: en BUSCARV, en lugar de usar un solo número de columna, si usa una matriz de números de columna, devolverá una matriz de valores de búsqueda.
