BUSCARV: busque el enésimo elemento (sin columnas auxiliares)
En esta serie, Dominar la BUSCARV, hemos cubierto mucho terreno. Pero esta publicación es una de las más diabólicas de todas. BUSCARV, por su diseño, pretende devolver la primera coincidencia que encuentre. Pero, si queremos la 2ª, 3ª o enésima BUSCARV no es tan fácil. A menudo la gente recurre a columnas de ayuda, pero eso realmente no es necesario (¡y no es tan divertido!)
Encontrar la enésima búsqueda
Mire la captura de pantalla a continuación. Nuestros datos están ordenados por edad, pero ¿cómo podríamos encontrar la edad del tercer Dave en la lista?
La fórmula en la celda E6 es:
{=BUSCARV(PEQUEÑO(SI(A2:A8=E3,FILA(A2:A8)),E4),ELEGIR({1,2},FILA(A2:A8),B2:B8),2,0)}
En primer lugar, esta es una fórmula matricial. No escriba {} al principio y al final, ingrese la fórmula con Ctrl+Shift+Enter y Excel las agregará automáticamente.
En segundo lugar, sí, ¡esta fórmula da miedo! Pero quédate conmigo, haré todo lo posible para explicarlo.
¿Cómo funciona la fórmula?
La clave para que esta fórmula funcione correctamente en la función PEQUEÑA junto con la función FILA. Vamos desarrollando la fórmula poco a poco.
Función SI
{=BUSCARV(PEQUEÑO(SI(A2:A8=E3,FILA(A2:A8)),E4),ELEGIR({1,2},FILA(A2:A8),B2:B8),2,0)}
La sección resaltada en rojo es casi una función IF estándar. Sin embargo, está trabajando en una variedad de células, en lugar de en una sola célula. Si alguna celda en A2-A8 es igual al valor de búsqueda, Dave en nuestro ejemplo, se devuelve el número de fila de esa celda. Si el nombre no es Dave, se devuelve FALSE. El nombre Dave está en la Fila 3, Fila 4 y Fila 8, por lo que la función SI devolvería una matriz de {FALSO, 3, 4, FALSO, FALSO, FALSO, 8}.
Función PEQUEÑA
{=BUSCARV(PEQUEÑO(SI(A2:A8=E3,FILA(A2:A8)),E4),ELEGIR({1,2},FILA(A2:A8),B2:B8),2,0)}
Hemos envuelto el resultado de la función SI en la función PEQUEÑA. La celda E4 es el enésimo valor que estamos buscando. En nuestro ejemplo, E4 es 3, ya que estamos buscando la tercera búsqueda. Esto ahora buscará el tercer valor más pequeño de la matriz de función IF devuelta anteriormente. El tercer resultado más pequeño de la matriz es 8; {FALSO,3,4,FALSO,FALSO,FALSO, 8 }.
Esto completa el primer argumento de la función BUSCARV; el valor de búsqueda es 8. Aunque estamos buscando la tercera BUSCARV de Dave, el tercer Dave está en la octava fila. Entonces, estamos buscando 8. Esperemos que no sea demasiado confuso.
ELEGIR Función
La función ELEGIR funciona de manera similar a la que encontramos al hacer una BUSCARV a la izquierda . Sin embargo, usaremos el número de fila como columna de búsqueda.
Dentro de la función ELEGIR, los números dentro de {} determinan qué columna es cuál.
{=BUSCARV(PEQUEÑO(SI(A2:A8=E3,FILA(A2:A8)),E4),ELEGIR({1,2},FILA(A2:A8),B2:B8),2,0)}
La columna 1, la columna de búsqueda, es solo el número de fila. Esto devolvería una matriz de {2, 3, 4, 5, 6, 7, 8}. Nuestro valor de búsqueda es 8, por lo que nuestra BUSCARV podrá encontrar un valor.
{=BUSCARV(PEQUEÑO(SI(A2:A8=E3,FILA(A2:A8)),E4),ELEGIR({1,2},FILA(A2:A8),B2:B8),2,0)}
La columna 2 se define como B2-B8.
Función BUSCARV
{=BUSCARV(PEQUEÑO(SI(A2:A8=E3,FILA(A2:A8)),E4),ELEGIR({1,2},FILA(A2:A8),B2:B8),2,0)}
La función BUSCARV se utiliza para realizar una coincidencia exacta de la fila número 8 y devolver el valor de la columna 2 (celdas B2-B8).
El resultado
El resultado de nuestra fórmula es 47. Podemos adaptar esta fórmula para encontrar la enésima búsqueda de cualquier conjunto de datos.
Fue difícil llegar allí por un tiempo, pero espero que lo hayas superado.
Algunos comentarios/consejos
¿Existe una manera más eficiente?
Sí hay. Una vez que hayamos obtenido el número de fila de un valor coincidente, probablemente sería más fácil aplicar una fórmula ÍNDICE, en lugar de una BUSCARV. Pero el punto es que BUSCARV es una opción posible. Si BUSCARV es una de las pocas funciones que conoce, entonces úsela.
Personajes espaciadores
Podríamos/deberíamos usar caracteres espaciadores en esta fórmula. Si bien es muy poco probable que se obtenga un resultado incorrecto con nuestro ejemplo, podría ser posible con otros conjuntos de datos, por lo que es mejor utilizar caracteres espaciadores. Dada la complejidad del ejemplo, decidí omitirlos. Si los quisiéramos la fórmula sería:
{=BUSCARV(E3"^"PEQUEÑO(SI(A2:A8=E3,FILA(A2:A8)),E4),ELEGIR({1,2},A2:A8"^"FILA(A2:A8), B2:B8),2,0)}
Columnas auxiliares
Si este ejemplo parece demasiado complejo, la opción más sencilla es utilizar una columna auxiliar.
La fórmula en B2 es:
=A2"^"CONTAR.SI($A$2:A2,A2)
Copie esta fórmula hasta B8. Entonces la fórmula en F6 podría ser:
=BUSCARV(F3"^"F4,B2:C8,2,0)
Simple.
Descargue la hoja de referencia de BUSCARV avanzada
Descargue la hoja de referencia de BUSCARV avanzada. Incluye la mayoría de los consejos y trucos que hemos cubierto en esta serie, incluidos cálculos más rápidos, criterios múltiples, búsqueda por la izquierda y mucho más.
Descárgalo y fíjalo en el trabajo; incluso puedes reenviarlo a tus amigos y compañeros de trabajo.
Descargue el archivo de ejemplo: únase al programa Insiders gratuito y obtenga acceso al archivo de ejemplo utilizado para esta publicación.
Nombre del archivo: 0166 BUSCARV Avanzada.pdf
Tener acceso
Otras publicaciones en la serie Mastering VLOOKUP
- Cómo utilizar BUSCARV
- BUSCARV: ¿Qué hace la declaración Verdadero/Falso?
- BUSCARV: Cómo calcular más rápido
- Cómo BUSCAR V a la izquierda
- BUSCARV: cambia el número de columna automáticamente
- BUSCARV con múltiples criterios
- Usando comodines con BUSCARV
- Cómo usar BUSCARV con columnas y filas
- Expandir automáticamente el rango de datos BUSCARV
- BUSCARV: busque el enésimo elemento (sin columnas auxiliares)
- BUSCARV: enumera todos los elementos coincidentes
- Hoja de referencia avanzada de BUSCARV
Deja una respuesta