Búsquedas con COINCIDIR en varias columnas

A partir de mañana y por los próximos diez estaré ocupado en una investigación sobre el tema “Los mejores platos y vinos en restoranes no turísticos de Toscana” (es decir, me tomo vacaciones en esa bellísima zona de Italia).

Mientras tanto veamos un tema sobre el cual he recibido varias consultas últimamente: realizar búsqueda usando COINCIDIR a través de varias columnas.

Para calcular qué posición ocupa un elemento determinado en un rango, Excel nos provee con la función COINCIDIR. Pero si el rango de búsqueda comprende más de una columna (o fila), la función da un resultado de error.

Para el caso supongamos que tenemos una serie de valores (números o texto) en el rango D1:E10. Para saber que posición ocupa el valor “14” nos veremos tentados a usar la fórmula

=COINCIDIR(B2,D1:E10,0)

donde B2 contiene el valor de búsqueda. Si bien 14 ocupa el quinto lugar en la segunda columna, el resultado es #N/A

La función COINCIDIR funciona sólo con rangos de búsqueda (matrices) de una única columna o fila.

Para hacer la búsqueda a través de varias columnas podemos combinar COINCIDIR con las funciones SI y ESERROR o, si usamos Excel 2007 o 2010, la nueva función SI.ERROR de esta manera

=SI.ERROR(COINCIDIR(B2,D1:D10,0),SI.ERROR(COINCIDIR(B2,E1:E10,0),"inexistente"))

Si la búsqueda debe hacerse en tres columnas agregamos otra función SI.ERROR

=SI.ERROR(COINCIDIR(B2,D1:D10,0),SI.ERROR(COINCIDIR(B2,E1:E10,0),SI.ERROR(COINCIDIR(B2,F1:F10,0),"inexistente")))

En Excel Clásico (97-2003) puede hacerse combinando Si con ESERROR, pero la nueva función SI.ERROR nos permite crear una fórmula mucho más compacta.

Uno de los inconvenientes de esta solución es que no nos dice en qué columna se encuentra el valor. Además, nos da la ubicación relativa del elemento en la matriz de búsqueda, pero por lo general queremos saber en qué fila se encuentra el elemento.

Para calcular la dirección de la celda que contiene el elemento buscado podemos usar DIRECCION combinada con la solución anterior

=SI.ERROR(DIRECCION(COINCIDIR(B2,D1:D10,0),4),SI.ERROR(DIRECCION(COINCIDIR(B2,E1:E10,0),5),"inexistente"))

Otra alternativa es crear una UDF (función definida por el usuario) como ésta

Function direccion_celda(Valor_Buscado, Matriz_Busqueda As Range)
    Dim rngCell As Range
   
    For Each rngCell In Matriz_Busqueda
        If rngCell.Value = Valor_Buscado Then
            direccion_celda = rngCell.Address
            Exit Function
        Else
            direccion_celda = "inexistente"
        End If
    Next rngCell

End Function