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

Más sobre el extraño caso del signo más en Excel

En la nota anterior vimos el extraño caso del signo más en Excel, producto de los problemas de compatibilidad con Lotus 1-2-3

y mostramos la solución.

Pero sucede que esta solución tiene efectos secundarios, como me señala el amigo Sebastián, asiduo lector del blog.

Normalmente, si ingresamos el valor “28-8-2012” en una celda, Excel lo transforma automáticamente en la fecha 28/8/2012

Veamos que pasa después de marcar las opciones de compatibilidad con Lotus 1-2-3

Excel realiza la operación (28-8-2012 = -1992), en lugar de convertirlo en fecha.

A diferencia del caso anterior, esto no parece estar relacionado al formato de la celda. También si la celda tiene el formato General, Excel realiza la operación en lugar de poner la fecha.

EL extraño caso del signo más (+) en Excel.

Excel tiene ciertas zonas un poco tenebrosas donde no todo funciona, o parece funcionar, como esperamos. Pero el usuario avisado puede evitar entrar en esos oscuros callejones…

Vimos el caso de los resultados incorrectos de CONTAR.SI. Hoy vamos a ver cierto comportamiento extraño del símbolo “más” (+).

Todos sabemos que si iniciamos una fórmula con el “más” (+) Excel lo transforma automáticamente en “igual” (=). El símbolo + era el usado en Lotus 1-2-3, el antecesor de Excel.

Por ejemplo, si introducimos en una celda “+10/2.5”, veremos en la barra de fórmulas que Excel lo transforma en “=10/2.5” y el resultado es 4



Ahora veamos que pasa con esta operación cuando la celda tiene un formato distinto al General



Excel graciosamente nos informa que hay un error en la fórmula. No voy a intentar explicar este fenómeno por la sencilla razón que no tengo la menor idea por qué sucede. Pero si puedo exponer la solución.

En Excel 2010 activamos Archivo-Opciones-Avanzada y al final del formulario señalamos la opción “Introducción de fórmulas de transición” y apretamos Aceptar.

En Excel 2007

En Excel 2003