La función BUSCARV nos permite encontrar un valor dentro de un rango de celdas y aunque el valor buscado se repita varias veces, la función BUSCARV siempre nos devolverá solo la primera ocurrencia encontrada. A veces queremos utilizar la función BUSCARV con valores repetidos en Excel para encontrar la enésima ocurrencia de algún valor, pero eso no sucederá automáticamente.
La verdad es que no podemos hacer este tipo de búsquedas utilizando solamente la función BUSCARV, es necesario encontrar alternativas que nos permitan indicar dentro de la fórmula el número de ocurrencia que necesitamos. En esta ocasión utilizaremos como ejemplo una lista de nombres de personas con sus respectivas edades tal como lo puedes ver en la siguiente imagen.
En nuestros datos existen nombres repetidos, así que la fórmula que construyamos tendrá que considerar el número de ocurrencia indicado en la celda E3. A continuación revisaremos cada una de las opciones que tenemos para resolver este problema.
Opción 1: Columna auxiliar numerada
La primera alternativa que tenemos es utilizar una columna auxiliar donde podamos incluir a cada valor del rango su número de ocurrencia, por ejemplo, la primera aparición del nombre Blanca deberá transformarse en Blanca1 y la segunda aparición en Blanca2 y así sucesivamente. Para poder contar las apariciones de cada valor haremos uso de la función CONTAR.SI de la siguiente manera:
=A2 & CONTAR.SI(A$2:A2, A2)
El objetivo de la función CONTAR.SI será contar las apariciones de la celda A2 en el rango indicado en su primer argumento, que en este caso será solamente la celda A2 (A$2:A2), por lo que obtendremos como resultado la cuenta de uno.
Observa que para el primer argumento de la función CONTAR.SI he utilizado el inicio del rango como una referencia mixta (A$2) lo cual fijará el número de fila donde se inicia el rango a considerar en la cuenta de manera que al copiar esta fórmula hacia abajo la única parte que será modificada será el final del rango. En la siguiente imagen puedes observar el resultado de copiar la fórmula hacia abajo.
De esta manera la función CONTAR.SI nos ha ayudado a identificar el número de ocurrencia de cada uno de los elementos de nuestro rango y esta nueva columna auxiliar nos servirá para realizar la búsqueda de la ocurrencia exacta utilizando la función BUSCARV de la siguiente manera:
=BUSCARV(F1&F2,B2:C10,2,FALSO)
En este caso el valor buscado será la concatenación de las celdas F1 y F2 ya que el rango de búsqueda será la columna auxiliar recién creada. Observa cómo esta fórmula nos devuelve el valor correcto de la calificación para la tercera ocurrencia de Carlos:
De esta manera hemos podido utilizar la función BUSCARV con valores repetidos y obtener el resultado esperado con solo haber insertado los números de ocurrencia en una columna auxiliar.
Opción 2: Utilizar una fórmula matricial
La segunda opción que podemos utilizar es una fórmula matricial que nos permita identificar aquellos valores dentro del rango que contengan el valor buscado y obtener el número de fila que ocupan dentro de la hoja. Ya que esta es una fórmula de mayor complejidad que la anterior, la desarrollaremos paso a paso para dejar en claro su funcionamiento.
La primera parte de la fórmula implica el uso de la función SI para comparar los elementos del rango con el valor buscado. En caso de que la comparación sea verdadera utilizaré la función FILA para obtener su posición dentro de la hoja.
=SI(A2:A10=E1, FILA(A2:A10))
Como ejemplo colocaré esta fórmula matricial en el rango G2:G10 para que puedas observar el resultado devuelto. Recuerda que al ingresar esta fórmula será necesario pulsar la combinación de teclas Ctrl + Mayús + Entrar:
Solo para aquéllas celdas en el rango A2:A10 que tengan el nombre de Carlos, se mostrará su número fila en la hoja. El siguiente paso es encontrar dentro de los resultados anteriores la ocurrencia exacta que estamos buscando y para eso utilizaremos la función K.ESIMO.MENOR de la siguiente manera:
=K.ESIMO.MENOR(SI(A2:A10=E1, FILA(A2:A10)), E2)
El primer argumento de esta función es la primera fórmula anterior y su segundo argumento es la celda que indica la ocurrencia que necesitamos, que en nuestro ejemplo es la celda E2. Al ingresar esta fórmula en la celda G2 y pulsar Ctrl + Mayús + Entrar obtendremos el siguiente resultado:
Esto nos indica que es la fila 8 de nuestra hoja la que contiene la tercera ocurrencia del nombre Carlos dentro del rango de búsqueda. Para resolver el problema solamente debemos obtener la fila 8 de la columna de Edades, así que puedo utilizar la función INDIRECTO para obtener el valor de dicha celda en base al resultado de la fórmula anterior.
=INDIRECTO("B" & K.ESIMO.MENOR(SI(A2:A10=E1, FILA(A2:A10)), E2))
Lo único que he hecho en esta fórmula es concatenar la letra “B” al inicio del resultado de la fórmula anterior de manera que obtengamos el valor que se encuentra en la fila 8 de la columna B de nuestra hoja. Recuerda pulsar la combinación de teclas Ctrl + Mayús + Entrar al ingresar esta última fórmula:
Aunque esta fórmula es más compleja que la primera opción mostrada, nos evita el agregar una columna auxiliar a nuestra hoja.
Opción 3: Crear una función UDF
Si necesitas realizar continuamente búsquedas de este tipo en Excel, entonces te conviene crear tu propia función UDF que nos permite indicar la ocurrencia que estamos buscando y obtener el resultado correcto. Considera el siguiente código VBA para la creación de una nueva función que nos permitirá buscar entre valores repetidos en Excel:
Function BUSCAROCURRENCIA(valor_buscado As Variant, matriz_buscar As Range, _
ocurrencia As Integer, indicador_columna As Integer)
Dim cont As Integer
BUSCAROCURRENCIA = "No existe"
For i = 1 To matriz_buscar.Rows.Count
If matriz_buscar.Cells(i, 1).Value = valor_buscado Then
cont = cont + 1
If cont = ocurrencia Then
BUSCAROCURRENCIA = matriz_buscar.Cells(i, indicador_columna).Value
Exit Function
End If
End If
Next
End Function
Nuestra función tiene cuatro argumentos que nos ayudarán a hacer la búsqueda:
- Valor_buscado: Es el valor que estamos buscando
- Matriz_buscar: El rango de celdas con los datos. La búsqueda, al igual que con la función BUSCARV, se hará en la primera columna del rango indicado.
- Ocurrencia: El número de ocurrencia del valor_buscado que necesitamos.
- Indicador_columna: El número de columna a ser devuelto por la función.
La función hace un recorrido por todo el rango de búsqueda con un bucle For-Next. En cada iteración se compara el elemento de la primera columna del rango con el valor buscado y si coinciden, se incrementa en uno el contador de ocurrencias. Si dicho contador es igual al número de ocurrencia que necesitamos, entonces se devuelve el valor de la columna correspondiente. Observa el resultado de utilizar esta función con nuestros datos de ejemplo:
La función busca el valor indicado en la celda E1 dentro del rango A2:B10 y recorre los valores de la primera columna en búsqueda de la ocurrencia indicada en la celda E2. Una vez encontrado dicho valor nos devolverá la misma fila pero de la columna 2 tal como lo indica el último argumento de la función.
Es así como hemos evaluado tres alternativas al uso de la función BUSCARV con valores repetidos en Excel. Ya que la función BUSCARV no puede realizar este tipo de búsquedas por sí sola, será necesario elegir alguno de los métodos mostrados en este artículo para poder encontrar la ocurrencia exacta del valor que estés buscando.