VBA – VLOOKUP o XLOOKUP – Buscar en otra Hoja o Libro

Este artículo demostrará cómo utilizar las funciones VLOOKUP y XLOOPUP para buscar valores en otras hojas o libros de trabajo en VBA.

Las funciones VLOOKUP y XLOOKUP en Excel son funciones útiles que nos permiten buscar un valor coincidente en un rango y devolver un valor correspondiente de otra columna. Estas funciones se pueden utilizar directamente en una fórmula en Excel, o se pueden utilizar en la codificación VBA con el método WorksheetFunction. Podemos utilizar estas funciones para buscar valores que están contenidos en una hoja diferente de donde queremos que se produzca el resultado, o incluso en un libro de trabajo diferente.

VLOOKUP y XLOOKUP desde Otra Hoja

VLOOKUP desde Otra Hoja

Para utilizar el VLOOKUP en una hoja diferente a la de los datos de búsqueda, podemos escribir el siguiente código:

Sub BuscarPrecio()
   ActiveCell = Application.WorksheetFunction.VLookup(Range("B3"), Sheets("ListaProductos").Range("B2:C6"), 2, False)
End Sub

O

Sub BuscarPrecio()
   ActiveCell = Application.VLookup(Range("B3"), Sheets("ListaProductos").Range("B2:C6"), 2, False)
End Sub

Dando lugar a lo siguiente

 

También podríamos, para mayor facilidad, utilizar variables en nuestro código.

Sub BuscarPrecio()
   Dim ws As Worksheet
   Dim rng As Range
   Dim strProduct As String
   strProduct = Range("B3")
   Set ws = Sheets(1)
   Set rng = ws.Range("B2:C6")
   ActiveCell = Application.WorksheetFunction.VLookup(strProduct, rng, 2, False)
End Sub

Esto devolvería el mismo resultado.

Si estamos buscando un valor que no existe, es posible que deseemos utilizar la función Application.VLookup para devolver #N/D a la celda en lugar de la función Application.WorksheetFunction.VLookup que daría lugar a un error de VBA.

Si deseamos devolver la fórmula en lugar del valor a la celda de destino, entonces este es el código que tendríamos que introducir.

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

La fórmula utiliza la sintaxis R1C1 (filas y columnas) en lugar de la sintaxis A1 (rango).

Esto resultaría en:

 

XLOOKUP desde Otra Hoja

Usar el XLOOKUP para buscar datos en una hoja diferente es muy parecido. Recuerda que el XLOOKUP busca en múltiples rangos en lugar de en una columna específica para encontrar el valor.

Sub BuscarPrecio_XLookup()
   ActiveCell = Application.WorksheetFunction.XLookup(Range("B3"), Sheets("ListaProductos").Range("B2:B6"), Sheets("ListaProductos").Range("C2:C6"))
End Sub

o con variables:

Sub BuscarPrecio_XLookup()
    Dim ws As Worksheet
    Dim strProduct As String
    Dim rngProduct As Range
    Dim rngPrice As Range
    strProduct = Range("B3")
    Set ws = Sheets("ListaProductos")
    Set rngProduct = ws.Range("B2:B6")
    Set rngPrice = ws.Range("C2:C6")
    ActiveCell = Application.WorksheetFunction.XLookup(strProduct, rngProduct, rngPrice)
End Sub

El resultado de cualquiera de estos ejemplos sería entonces:

Para devolver una fórmula utilizando XLOOKUP, una vez más tendríamos que utilizar la sintaxis fila/columna (R1C1) en lugar de la sintaxis Rango (A1).

Sub InsertarFormulaXlookup()
  ActiveCell.FormulaR1C1 = "=XLOOKUP(RC[-1],ListaProductos!RC[-1]:R[3]C[-1],ListaProductos!RC:R[3]C)"
End Sub

El resultado sería:

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 C3 donde busca los valores del rango C2:E6. Debido al hecho de que se trata de múltiples columnas, se rellenarán automáticamente las columnas D y E con los resultados encontrados. La fórmula se DERRAMA en las columnas D y E 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 InsertarFormulaXlookup_2()
   ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-1],ListaProductos!RC[-1]:R[3]C[-1],ListaProductos!RC:R[3]C[2])"
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 puedes buscar múltiples columnas si estás usando el método WorksheetFunction.

VLOOKUP y XLOOKUP desde Otro Libro

Trabajar con otro libro en lugar de una hoja de trabajo diferente es muy parecido.

VLOOKUP desde Otro Libro

Considere el siguiente Ejemplo:

 

Para replicar esto con código VBA, crearíamos la siguiente macro:

Sub BuscarPrecio_EnOtroLibro()
  ActiveCell = Application.WorksheetFunction.VLookup(Range("B3"), Workbooks("Productos.xlsm").Sheets("ListaProductos").Range("B2:C6"), 2, False)
End Sub

donde el archivo Productos.xlsm tendría que estar abierto en Excel para que esta búsqueda funcione. También podríamos utilizar variables:

Sub BuscarPrecio_EnOtroLibro_2()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim rng As Range
    Dim strProduct As String
    Set wb = Workbooks("Productos.xlsm")
    Set ws = wb.Sheets("ListaProductos")
    Set rng = ws.Range("B2:C6")
    strProduct = Range("B3")
    ActiveCell = Application.WorksheetFunction.VLookup(strProduct, rng, 2, False)
End Sub

El resultado de cualquiera de estos ejemplos sería:

 

Si quisiéramos que la fórmula real apareciera en la celda en lugar del valor, entonces nuestro código VBA incorporaría el nombre del libro de trabajo así como el nombre o número de la hoja.

Por ejemplo:

Sub BuscarPrecio_EnOtroLibro_Formula()
  ActiveCell.FormulaR1C1 = "=VLOOKUP(RC[-1],[Productos.xlsm]ListaProductos!R3C2:R6C3,2,FALSE)"
End Sub

XLOOKUP desde Otro Libro

La vinculación a otro archivo utilizando el XLOOKUP en lugar del VLOOKUP sigue las mismas líneas. Para devolver el valor a Excel:

Sub BuscarPrecio_EnOtroLibro_Xlookup()
   ActiveCell = Application.WorksheetFunction.XLookup(Range("B3"), Workbooks("Productos.xlsm").Sheets("ListaProductos").Range("B2:B6"), Workbooks("Productos.xlsm").Sheets("ListaProductos").Range("C2:C6"))
End Sub

o con variables:

Sub BuscarPrecio_EnOtroLibro_Xlookup()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim strProduct As String
    Dim rngProduct As Range
    Dim rngPrice As Range
    strProduct = Range("B3")
    Set wb = Workbooks("Productos.xlsm")
    Set ws = wb.Sheets("ListaProductos")
    Set rngProduct = ws.Range("B2:B6")
    Set rngPrice = ws.Range("C2:C6")
    ActiveCell = Application.WorksheetFunction.XLookup(strProduct, rngProduct, rngPrice)
End Sub

El resultado de cualquiera de estos ejemplos sería entonces:

 

Para devolver la fórmula a Excel en lugar del valor, podemos modificar nuestra macro para incluir el nombre del libro además del nombre o número de la hoja.

Sub BuscarPrecio_EnOtroLibro_Xlookup_Formula()
   ActiveCell.Formula2R1C1 = "=XLOOKUP(RC[-1],[Productos.xlsm]ProductList!RC[-1]:R[3]C[-1],[Productos.xlsm]ListaProductos!RC:R[3]C[2])"
End Sub