BUSCARV con dos parámetros

Un lector me consulta si es posible hacer búsquedas en una tabla de acuerdo a dos parámetros. La respuesta es positiva y vamos a mostrar las distintas posibilidades en esta nota.
Aclaremos que el título de la nota es un poco engañoso ya que además de BUSCARV mostraremos soluciones con tablas dinámicas y con las funciones INDICE y COINCIDIR, en forma natural y en forma matricial.

Supongamos una tabla con tres columnas: artículo, fecha y precio. Cada artículo aparece varias veces pudiendo variar la fecha y el precio como en esta tabla:

1 - Solución con tabla dinámica (o "solución rápida").

Creamos una tabla dinámica basada en nuestra tabla de datos

En el área de las filas ponemos los campos Artículo y Fecha y el campo Precio en el área de los datos.
Ordenamos el campo Fecha de más reciente a más antiguo

con lo cual las fechas más reciente aparecerán al principio de cada grupo de artículos.

Ahora agregamos un cuadro de segmentación de datos y ocultamos todas la filas de la tabla excepto la que contiene el primer dato; agregamos algunos formatos para mejorar la presentación y ya tenemos nuestro modelo dinámico que siempre mostrará el último precio de cada artículo

La ventaja de esta solución reside en que no usa fórmulas sino tablas dinámicas por lo que funciona velozmente también con tablas de gran tamaño.

2 - Solución con BUSCARV y columna auxiliar.

Para usar BUSCARV en forma natural (es decir, no matricial) debemos crear una columna auxiliar con valores únicos por lo que combinamos el código del articulo con la fecha del precio )la columna ID)

Para esta solución es indispensable que la tabla esté organizada de menor a mayor de acuerdo al campo ID. Esto se debe a que usaremos BUSCARV con búsqueda aproximada, con esta fórmula

=BUSCARV(G3&MAX(tblPrecios[Fecha]),tblPrecios,4)

Como puede apreciarse, creamos el valor de búsqueda combinando al artículo buscado (en la celda G3) con el valor máximo del campo de las fechas. Como Hacemos una búsqueda aproximada, dejando el cuarto argumento de BUSCARV en blanco, obtenemos el valor más cercano que coincide con el valor buscado. Es por este motivo que la tabla tiene que estar ordenada de menor a mayor según el campo de búsqueda (ID).
Podemos ocultar la columna B de manera que el campo auxiliar no sea visible.

3 -Solución sin campo auxiliar con la función INDICE y COINCIDIR

Para ahorrarnos el campo auxiliar (considerado profano a las buenas prácticas de Excel por algunos puristas, concepto con el cual no concuerdo en absoluto), podemos combinar las funciones INDICE y COINCIDIR para crear esta función matricial (introducir con Ctrl-Mayúsculas-Enter):

=INDICE(tblPrecios4[Precio],COINCIDIR(F3&MAX(tblPrecios4[Fecha]),tblPrecios4[Articulo]&tblPrecios4[Fecha]))

Nótese que también aquí hacemos una búsqueda aproximada en la función COINCIDIR por lo que la tabla debe estar ordenada de menor a mayor según el campo Artículo y también de antiguo a reciente según el campo Fecha.

4 - Solución sin necesidad de ordenar la tabla.

En ciertas situaciones puede ser inconveniente o poco práctico tener que ordenar la tabla. Podemos extraer el valor buscado de acuero a artículo y fecha sin ordenar la tabla con esta fórmula matricial:

=INDICE(tblPrecios46[Precio],COINCIDIR(F3&MAX((tblPrecios46[Articulo]=F3)*tblPrecios46[Fecha]),tblPrecios46[Articulo]&tblPrecios46[Fecha],0))

Como puede apreciarse COINCIDIR realiza una búsqueda exacta, con el tercer parámetro puesto a 0, por lo que no hace falta ordenar la tabla.

En la función COINCIDIR, la expresión

F3&MAX((tblPrecios46[Articulo]=F3)*tblPrecios46[Fecha])

crea el valor de búsqueda;

la expresión

tblPrecios46[Articulo]&tblPrecios46[Fecha]

dentro de la fórmula matricial crea un vector que contiene todas las combinaciones de artículo/fecha (cono en el campo ID de la primera solución). Esto nos permite hacer una búsqueda exacta eximiéndonos de tener que ordenar la tabla.

El archivo con los ejemplos puede descargarse aquí.

Búsqueda horizontal con COINCIDIR

Lo más memoriosos de mis lectores seguramente recordarán la épica nota sobre búsqueda con la función COINCIDIR en columnas múltiples. En esa nota señalábamos que  la función COINCIDIR nos permite calcular la posición que ocupa un elemento determinado en un rango, pero que si el rango de búsqueda comprende más de una columna (o fila), la función da un resultado de error.
En la nota mostrábamos dos formas de realizar la búsqueda con COINCIDIR a lo largo de varias columnas: con fórmulas y con macros.

Ahora consideremos este problema: dada esta matriz de valores numéricos

queremos encontrar en qué fila aparece por primera vez el número 8. Como puede apreciarse el 8 aparece por primera vez en la fila 2.

Si aplicamos la fórmula que postulamos en la nota anterior, veremos que recibimos un resultado erróneo: ka fórmula da el resultado 3 cuando debería ser 2.

=SI.ERROR(COINCIDIR(H4,C4:C13,0),SI.ERROR(COINCIDIR(H4,D4:D13,0),SI.ERROR(COINCIDIR(H4,E4:E13,0),"ND")))

La fórmula realiza la búsqueda en el primer vector y si no encuentra una coincidencia, realiza la búsqueda en el segundo vector y así sucesivamente. Es decir, la búsuqeda en la matriz es "vertical", cuando lo que queremos hacer es una búsqueda "horizontal".

La solución obvia es usar vectores "horizontales", por ejemplo =SI.ERROR(COINCIDIR(H4,C4:E4,0),SI.ERROR(COINCIDIR(H4,C5:E5,0)....etc. El inconveniente de esta fórmula es obvio: en lugar de tres funciones COINCIDIR, tendríamos que combinar 10 funciones, tantas como las filas de la tabla. Imaginemos ahora una tabla con 1000 filas!

La solución en este caso es usar una función definida por el usuario que llamaremos "fila_celda"

Function fila_celda(Valor_Buscado, Matriz_Busqueda As Range)
    Dim rngCell As Range
  
    For Each rngCell In Matriz_Busqueda
        If rngCell.Value = Valor_Buscado Then
            fila_celda = rngCell.Row - Matriz_Busqueda(1).Row + 1
            Exit Function
        End If
    Next rngCell
   
    fila_celda = "inexistente"

End Function

Esta función usa dos argumentos: Valor_buscado y Matriz_Busqueda

La función realiza la búsqueda a lo largo de las filas como puede verse en el vector del argumento Matriz?Busqueda: 11,1,11;9,8,3;...(nótese el uso de la coma y el punto y coma).

El código de la función debe guardarse en un módulo común del editor de Vb. Para que estpe disponibles para todos los cuadernos abiertos, podemos guardarlo en el libro Personal.

Una vez guardada, la función aparecerá en el asistente de las funciones bajo la categoría "Definida por el usuario"

Evitar constantes en BUSCARV.

Si hay una línea en Excel que marca el paso de principiante a usuario intermedio, es el uso de BUSCARV. Esta función, como tantas otras funciones de búsqueda en Excel, extraer valores de una tabla de datos basándonos en un criterio de búsqueda.

Sin lugar a dudas, es una de las funciones más usadas en Excel. Sin embargo y desde el punto de vista de las buenas prácticas, tiene un problema al que pocos usuarios prestan atención: el tercer argumento de la función, el indicador_columna (la columna en la tabla de la cual se quiere extraer el dato). Veamos este ejemplo sencillo (que me vino a colación de una nota sobre normas de diseño en Excel que estoy escribiendo)

La fórmula en la celda F2 usa BUSCARV para extraer el número de teléfono de acuerdo al nombre que ponemos en E2.

Ahora, supongamos que insertamos en la tabla de datos una columna (Zona) entre los campos "Nombre" y "Teléfono"

Si bien los rangos de la función se han adaptado al cambio, el tercer argumento de la función es una constante. Y por lo tanto, ahora el resultado es la zona y no el teléfono.

En ciertos casos el error no es fácilmente identificable y puede ser crítico.

Las buenas prácticas en Excel nos indican que debemos evitar usar constantes en las fórmulas. En este caso podemos superar el problema usando la función COINCIDIR para crear una referencia dinámica.

Esto lo podemos hacer usando COINCIDIR junto con BUSCARV o, mejor en mi opinión, con INDICE.

Con BUSCARV usamos

=BUSCARV(F2;$A$2:$C$5;COINCIDIR(G$1;$A$1:$C$1;0);0)

con INDICE la fórmula es más corta

=INDICE($C$2:$C$5;COINCIDIR(F2;$A$2:$A$5;0))

Como Excel adapta los rangos dinámicamente al insertar, eliminar o mover filas y columnas, estas fórmulas mantienen siempre la referencia exacta y evitan errores inadvertidos.

Nótese que con la combinación de BUSCARV y COINCIDIR en nuestro ejemplo, podemos cambiar el data extraído con solo cambiar el texto en la celda G1.

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

BUSCARV (VLOOKUP) con texto dentro de texto

Uno de mis clientes implementó una aplicación para la administración de la planta de producción. En la planta hay una cierta cantidad de máquinas, que fueron asignadas a "centros". Cada centro puede tener una o más máquinas. Para crear la base de datos el ingeniero de producción preparó una tabla con las máquinas asignadas a cada centro

El departamento de computación pidió recibir una tabla con una línea para cada máquina

por lo que hubo que transformar la tabla de nuestro buen ingeniero en la tabla requerida por el departamento de computación (en la realidad hay en la planta 75 máquinas asignadas a 15 centros).

No podemos usar BUSCARV o INDICE con COINCIDIR ya que en una misma celda de la primer tabla hay más de una máquina. De hecho tenemos que extraer el valor correspondiente a un "sub-texto" dentro del texto.

Podemos hacerlo usando funciones nativas de Excel o con una UDF (función definida por el usuario). Con funciones nativas de Excel usamos esta fórmula matricial

 =INDICE(Hoja1!$A$4:$A$9;COINCIDIR(VERDADERO;ESNUMERO(HALLAR(E4;Hoja1!$B$4:$B$9));0))

Esta es una fórmula matricial y debe ser introducida apretando simultáneamente Ctrl+Mayúsculas+Enter

La función HALLAR busca el número de máquina (E4) en el rango de máquinas de la tabla de ingeniero (B4:B9). De hecho, HALLAR busca el "texto" 251 dentro del texto de las celdas del rango B4:B9. Si lo halla, el resultado es un número (la posición donde comienza el texto buscado dentro del texto de la celda de búsqueda).
La expresión ESNUMERO(HALLAR… genera un vector de resultados VERDADERO o FALSO. COINCIDIR calcula la posición del primer valor VERDADERO dentro de ese vector. Este resultado, a su vez, es utilizado en la función INDICE para calcular el centro correspondiente.

También podemos crear esta UDF, usando la función InStr de Vba. La función calcula la posición de la máquina en la lista, es decir, reemplaza la expresión

COINCIDIR(VERDADERO;ESNUMERO(HALLAR…

en la fórmula anterior. El resultado de la función lo usamos como argumento en la función INDICE, sin necesidad de usarla en forma matricial

 El código de la función match_sub_string es el siguiente:

 Function match_sub_string(SearchValue, rngSearchRange As Range)

    Dim lRangeRows As Long 'numero de filas en el rango
    Dim iR As Long
  
    lRangeRows = rngSearchRange.Cells.Count
  
    For iR = 1 To lRangeRows
        If InStr(rngSearchRange.Item(iR), SearchValue) > 0 Then
            match_sub_string = iR
            Exit Function
        Else
            'do nothing
        End If
    Next iR
  
       
End Function