VLOOKUP 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 tuve que buscar en toda la fila 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 una lista con sus nombres, y quería extraer las ventas máximas de ese representante de ventas en esos cuatro trimestres.
Se me ocurren dos formas diferentes de hacer esto: Usando INDICE o VOZ.
Glosario
Buscar toda la fila / columna usando la fórmula de índice
Aquí está la fórmula que he creado para hacer esto usando el Índice
=GRANDE(ÍNDICE($B$4:$F$13,FÓSFORO(H3,$B$4:$B$13,0),0),1)
Como funciona:
Veamos primero la función de ÍNDICE que está envuelta dentro de la función GRANDE.
=INDEX($C$4:$F$13,MATCH(H3,$B$4:$B$13,0),0)
Analicemos de cerca los argumentos de la función de ÍNDICE:
- Array – $B$4:$F$1
- Número de fila – MATCH(H3,$B$4:$B$13,0)
- Número de columna – 0
Fíjese que he usado el número de columna como 0.
El truco aquí es que cuando usas el número de columna como 0, devuelve todos los valores de todas las columnas. Así que si selecciono a John en el desplegable, la fórmula del índice devolvería los 4 valores de venta de John {91064,71690,67574,25427}.
Ahora puedo usar la función Grande para extraer el valor más grande
Consejo profesional – Utilice el número de Columna/Fila como 0 en la fórmula del Índice para devolver todos los valores de las Columnas/Filas.
Buscar toda la fila / columna usando la fórmula VLOOKUP
Mientras que la fórmula de Index es limpia y robusta, la forma de VLOOKUP es un poco compleja. También termina haciendo que la función sea volátil. Sin embargo, hay un truco asombroso que compartiría en esta sección. Aquí está la fórmula:
= GRANDE(VOZ(H3,B4:F13, RUTA(INDIRECTA(«2:»&COUNTA($B$4:$F$4))), FALSO),1)
Cómo funciona
- ROW(INDIRECT(«2:»&COUNTA($B$4:$F$4))) – Esta fórmula devuelve una matriz {2;3;4;5}. Nótese que como 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 pones juntos, se convierte en VERDADERO(H3,B4:F13,{2;3;4;5},FALSO). Ahora noten que en lugar de un solo número de columna, le he dado una matriz de números de columna. Y VLOOKUP obedientemente busca valores en todas estas columnas y devuelve una matriz.
- Ahora sólo usa la función LARGE para extraer el mayor valor.
Recuerda usar Control + Shift + Enter para usar esta fórmula.
Consejo profesional – En VLOOKUP, en lugar de usar un solo número de columna, si usas una matriz de números de columna, te devolverá una matriz de valores de búsqueda.
También te pueden gustar los siguientes tutoriales de Excel:
- VLOOKUP Vs. INDICE/MATCH
- Cómo hacer que VLOOKUP sea un caso sensible.
- Cómo usar VLOOKUP con múltiples criterios.
- Cómo sumar una columna en Excel