VBA – VLOOKUP y XLOOKUP

Este artículo demostrará cómo utilizar las funciones VLOOKUP y XLOOKUP en VBA.

Las funciones VLOOKUP y XLOOKUP (BUSCARV y BUSCARX) de Excel son extremadamente útiles. También pueden ser utilizados en codificación VBA.

VLOOKUP

Hay dos formas de llamar a la función VLOOKUP en VBA:

• Aplicación.WorksheetFunction.Vlookup
• Aplicación.Vlookup

Los dos métodos funcionan idénticamente, excepto por cómo manejan los errores.

Application.WorksheetFunction.VLookup

Este ejemplo demostrará el primer método.

 

Aquí hemos creado una fórmula VLOOKUP en Excel que replicaremos en VBA:

Sub ConsultarPrecio()
   ActiveCell = Application.WorksheetFunction.VLookup(Range("E3"), Range("B2:C6"), 2, False)
End Sub

El resultado será el siguiente:

Observe que el resultado real se ha escrito en la celda F3 en lugar de en la fórmula

Si deseamos devolver una fórmula a Excel en lugar de un valor, escribe el VLOOKUP en una fórmula :

Sub FormulaConsultarPrecio()
   ActiveCell = "=VLOOKUP(E3,B2:C6,2,FALSE)"
End Sub

O puedes escribir la fórmula en una celda usando la notación R1C1, que crea una fórmula con referencias relativas que puede usar en un rango de celdas:

Sub FormulaR1C1_ConsultarPrecio() 
  ActiveCell = "=VLOOKUP(RC[-1],RC[-4]:R[3]C[-3],2,FALSE)"
End Sub

También puede utilizar variables para definir las entradas de VLOOKUP:

Sub ConsultarPrecio()
    Dim strProduct As String
    Dim rng As Range
    strProduct = Range("E3")
    Set rng = Range("B2:C6")
    ActiveCell = Application.WorksheetFunction.VLookup(strProduct, rng, 2, False)
End Sub

Por supuesto, en lugar de escribir los resultados de la función en una celda, puedes escribir los resultados en una variable:

Sub ConsultarPrecio()
     Dim strProduct As String
     Dim rng As Range
     Dim strResult As String
    
     strProduct = Range("E3")
     Set rng = Range("B2:C6")
     strResult = Application.WorksheetFunction.VLookup(strProduct, rng, 2, False)
End Sub

Application.VLookup vs Application.WorksheetFunction.VLookup

En los ejemplos anteriores, podemos utilizar cualquiera de los dos métodos y obtendremos el mismo resultado. Sin embargo, ¿qué pasaría si buscáramos un producto que no existe? Cada función devolvería un resultado muy diferente.

Normalmente, cuando se utiliza VLOOKUP en Excel, si la búsqueda no encuentra la respuesta correcta, devuelve #N/A a la celda. Sin embargo, si usamos el ejemplo anterior y buscamos un producto que no existe, terminaremos con un Error VBA.

Podemos atrapar este error en nuestro código y devolver un mensaje más fácil de usar cuando el elemento que estamos buscando no se encuentra.

Sub ConsultarPrecio2()
  On Error GoTo eh
  ActiveCell = Application.WorksheetFunction.VLookup(Range("E3"), Range("B2:C6"), 2, False)
  Exit Sub
eh:
  MsgBox "Producto no encontrado - Favor intente con otro producto!"
End Sub

Alternativamente, podemos modificar nuestro código VBA para utilizar Application.Vlookup en lugar de Application.WorksheetFunction.VLookup.

Sub ConsultarPrecio3()
  ActiveCell = Application.VLookup(Range("E3"), Range("B2:C6"), 2, False)
End Sub

Cuando hacemos esto, el #N/D que hemos llegado a esperar cuando no se encuentra un valor de búsqueda, se devuelve a la celda.

 

WorksheetFunction.XLOOKUP

La función XLOOKUP se ha diseñado para sustituir a las funciones VLOOKUP y HLOOKUP en Excel. Solo está disponible para Office 365, por lo que el uso de esta función es bastante restrictivo si algunos de sus usuarios utilizan versiones anteriores de Excel.

Funciona de forma muy similar en VBA a la función VLOOKUP.

Sub ConsultaPrecio()
   ActiveCell = Application.WorksheetFunction.XLookup(Range("E3"), Range("B2:B6"), Range("C2:C6"))
End Sub

O

Sub ConsultaPrecio() 
   ActiveCell = Application.XLookup(Range("E3"), Range("B2:B6"), Range("C2:C6")) 
End Sub

Una vez más, si deseamos utilizar variables, podemos escribir el siguiente código

Sub ConsultaPrecio()
   Dim strProduct As String
   Dim rngProduct As Range
   Dim rngPrice As Range
   strProduct = Range("E3")
   Set rngProduct = Range("B2:B6")
   Set rngPrice = Range("C2:C6")
   ActiveCell = Application.WorksheetFunction.XLookup(strProduct, rngProduct, rngPrice)
End Sub

Si deseamos devolver una fórmula a Excel en lugar de un valor, tendríamos que escribir la fórmula en Excel con el código VBA.

Sub ConsultaPrecio()
   ActiveCell = "=XLOOKUP(E3,B3:B6,C3:C6)"
End Sub

O puede escribir la fórmula en una celda utilizando la notación R1C1.

Sub ConsultaPrecio() 
  ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-1],RC[-4]:R[3]C[-4],RC[-3]:R[3]C[-3])"
End Sub

Una de las principales ventajas de utilizar XLOOKUP frente a VLOOKUP es la posibilidad de buscar un rango de columnas y devolver el valor de cada columna.

Veamos el siguiente ejemplo:

 

Hemos creado la fórmula en la celda H3 donde busca los valores del rango C2:E6. Debido a que se trata de varias columnas, rellenará automáticamente las columnas I y J con los resultados coincidentes encontrados. La fórmula se DERRAMA en las columnas I y J sin que tengamos que usar CTRL+MAYÚS para una fórmula de matriz – esta capacidad para que la fórmula se DERRAME es una de las nuevas incorporaciones de Excel 365.

Para replicar esto con código VBA, podemos escribir lo siguiente en nuestra macro:

Sub ConsultarPrecio()
   ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-1],RC[-6]:R[3]C[-6],RC[-5]:R[3]C[-3])"
End Sub

Donde la fórmula está utilizando la sintaxis R1C1 (filas y columnas) en lugar de la sintaxis A1 (rango). Esto hará que las fórmulas se introduzcan en Excel como se muestra en el gráfico anterior.

No se pueden buscar varias columnas si se utiliza el método WorksheetFunction.