Las funciones de búsqueda de Excel nos permiten encontrar fácilmente la primera aparición de un valor dentro de una lista, pero si necesitamos encontrar la segunda o tercera aparición entonces la fórmula ya no será tan fácil de implementar.
Hoy aprenderás tres métodos para crear una fórmula de Excel que nos ayude a encontrar la enésima aparición de un valor dentro de una lista. Los datos que utilizaremos en los ejemplos son los siguientes:
Observa que la columna Nombre tiene valores repetidos y por lo tanto los utilizaremos para probar nuestras fórmulas buscando la enésima aparición de alguno de los nombres. Por otro lado, la columna Edad tiene valores únicos por lo que podremos utilizarla para validar que hemos encontrado el número de aparición adecuada.
Encontrar la enésima coincidencia con BUSCARV
Cuando hablamos de realizar búsquedas, la función BUSCARV siempre vendrá a la mente de cualquier usuario de Excel, pero es importante recordar que esta función devuelve siempre la primera aparición del valor buscado y no es posible cambiar dicho comportamiento.
Si queremos utilizar la función BUSCARV para encontrar la enésima aparición de un valor, será necesario utilizar una función adicional así como la creación de una columna auxiliar. Así que comenzaremos hablando un poco sobre la función CONTAR.SI que nos ayudará en esta tarea.
La función CONTAR.SI será de utilidad para contar las apariciones de cada uno de los valores de la lista. El objetivo es que la función me devuelva el número de aparición que corresponde a cada uno de los nombres y eso lo logramos con la siguiente fórmula:
=CONTAR.SI($C$2:C2,C2)
Esta fórmula la colocaré en una nueva columna, específicamente en la celda B2 tal como lo muestra la siguiente imagen:
La fórmula devuelve la cantidad de veces que aparece el nombre “Sofía” dentro del rango $C$2:C2. La clave para que esta fórmula funcione adecuadamente al momento de copiarla hacia abajo, es el tipo de referencia utilizada en el primer argumento ($C$2:C2). Observa que la primera referencia del rango es absoluta ($C$2) mientras que la segunda referencia es relativa (C2).
El haber construido la referencia de esta manera nos permitirá copiar la fórmula hacia abajo y obtener el resultado esperado. La siguiente imagen muestra el resultado de haber copiado la fórmula hasta la celda B20:
He resaltado las filas que coinciden con el mismo nombre “Sofía” para que puedas observar que la fórmula de la nueva columna devuelve el número de aparición para cada uno de los valores hasta llegar a la quinta aparición que está ubicada en la celda B19.
De esta manera la función CONTAR.SI nos devuelve el número de aparición para cada uno de los valores de la columna Nombre. Ahora solo resta concatenar los nombres con el número de aparición de cada uno de ellos y colocarlos en una columna auxiliar como se muestra a continuación:
Es importante insertar la columna Auxiliar como la primera columna porque la función BUSCARV hace siempre las búsquedas sobre la primera columna del rango indicado. Para realizar las búsquedas, tomaré el valor de la celda G1, lo concatenaré con el número de aparición contenido en la celda G2 y buscaré dicha cadena de texto sobre la columna Auxiliar. La fórmula es la siguiente:
=BUSCARV(G1&G2,A2:D20,4,FALSO)
Al concatenar el nombre y el número de aparición estaremos generando el mismo tipo de dato contenido en la columna Auxiliar y por lo tanto la búsqueda será exitosa. La siguiente imagen muestra el resultado de buscar la edad de la cuarta aparición de Sofía.
Para probar que nuestra fórmula funciona correctamente, puedes cambiar el número de aparición de la celda G2 o el nombre de la celda G1 y obtendrás el resultado esperado. La siguiente imagen muestra el resultado de buscar la tercera aparición de Miguel:
De esta manera hemos creado una fórmula para encontrar la enésima aparición de cualquier valor dentro de la lista utilizando la función BUSCARV.
Enésima aparición con las funciones INDICE/COINCIDIR
Una variante del ejemplo anterior es utilizar las funciones INDICE y COINCIDIR para realizar la búsqueda. La diferencia principal es que no será necesario insertar la columna Auxiliar al principio del rango ya que eso es irrelevante para las funciones mencionadas.
En la siguiente imagen puedes observar que he insertado la columna Auxiliar al final de los datos. Además puedes notar que en la misma columna he concatenado el nombre y el número de aparición devuelto por la función CONTAR.SI.
Este es un método más rápido para crear la columna Auxiliar y podrías hacerlo de la misma manera en el ejemplo anterior. No lo hice de esta manera en el ejemplo de la función BUSCARV porque preferí separar el cálculo en dos columnas para poder explicarlo mejor.
Regresando a nuestro ejemplo, la fórmula que nos ayudará a encontrar la enésima aparición de cualquier nombre será la siguiente:
=INDICE(B2:B20,COINCIDIR(F1&F2,C2:C20,0))
La función COINCIDIR concatena el nombre y el número de aparición de las celdas F1 y F2 para buscarlo en el rango de la columna Auxiliar (C2:C20). La función devuelve la posición del elemento dentro del rango, el cual utilizaremos para que la función INDICE nos devuelva la edad que se encuentra en la misma posición dentro del rango B2:B20. La siguiente imagen muestra el resultado de buscar la tercera aparición de Miguel:
Fórmula matricial para buscar la enésima aparición
El último método que revisaremos utiliza una fórmula matricial para realizar este tipo de búsqueda. Si no sabes lo que es una fórmula matricial te recomiendo leer el artículo Formulas matriciales en Excel para tener una mejor idea.
Lo más importante es recordar que las fórmulas matriciales se deben ingresar en la barra de fórmulas pulsando la combinación de teclas Ctrl + Mayús+ Entrar. Recuerda que la tecla Mayús también es conocida como la tecla Shift.
La primera parte de nuestra fórmula utilizará la función SI para encontrar aquellos valores que coinciden con el valor que estamos buscando. La primera fórmula que probaremos será la siguiente:
=SI($A$2:$A$20="Sofía", FILA($A$2:$A$20), "")
Ingresaré esta fórmula sobre el rango D2:D20 y pulsaré la combinación Ctrl + Mayús + Entrar y obtendré el siguiente resultado:
Observa con detenimiento los resultados ubicados en la columna D y verás que solo las filas que contienen el nombre “Sofía” son las que muestran el número de fila a la que corresponden. Es muy importante recordar que ese número se refiere al número de fila de la hoja de Excel y no a la posición que ocupan los valores dentro del rango A2:A20.
La fórmula que acabamos de construir nos ayudará a conocer aquellas filas que contienen el valor buscado y solo nos faltará obtener el número de aparición que necesitamos, para lo cual utilizaremos la función K.ESIMO.MENOR.
La función K.ESIMO.MENOR tomará la matriz devuelta por la función SI y buscará el número de aparición correspondiente. Para nuestro ejemplo buscaremos la cuarta aparición del nombre “Sofía” y por lo tanto utilizaré la siguiente fórmula matricial:
=K.ESIMO.MENOR(SI($A$2:$A$20="Sofía", FILA($A$2:$A$20), ""), 4)
El primer argumento de esta fórmula es la misma de la función SI anterior y el segundo argumento es el valor 4 porque necesitamos la cuarta aparición del nombre. El resultado lo puedes ver en la siguiente imagen:
El resultado devuelto es el número fila de la hoja de Excel donde se encuentra la cuarta aparición del nombre “Sofía”. Solo nos queda obtener la Edad que esté ubicada en la fila 15 de la hoja y eso lo lograremos fácilmente con la función INDICE de la siguiente manera:
=INDICE(B:B, K.ESIMO.MENOR(SI($A$2:$A$20="Sofía", FILA($A$2:$A$20), ""), 4))
Ya que estamos buscando la fila 15 de la hoja de Excel, la función INDICE hace la búsqueda sobre toda la columna B que contiene los datos de la Edad y por eso observas la referencia B:B como el primer argumento de la función. El segundo argumento de la fórmula es el resultado devuelto por la función K.ESIMO.MENOR. Observa la siguiente imagen donde la fórmula devuelve la edad que corresponde a la cuarta aparición del nombre “Sofía”:
Para dejar totalmente parametrizada la fórmula matricial, tal como lo hicimos en los ejemplos anteriores, reemplazaré el nombre “Sofía” y el valor 4 por una referencia de celda de manera que se puedan realizar nuevas búsquedas fácilmente. El resultado lo puedes ver en la siguiente imagen:
Con estos ejemplos solo faltará que decidas el método adecuado para implementar la fórmula que te ayudará a encontrar la enésima aparición de un valor dentro de una lista de Excel. Puedes descargar el archivo de trabajo que he utilizado en la elaboración de este artículo y donde encontrarás cada uno de los ejemplos realizados anteriormente.