vba · DOCS

Recrear VLOOKUP

Actualizado el

La función VLOOKUP de Excel es una herramienta muy conveniente para buscar valores específicos y recuperar datos relacionados. Sin embargo, al usar VBA (Visual Basic for Applications), puedes realizar búsquedas de datos aún más flexibles y automatizadas. Este artículo proporciona una explicación detallada de cómo implementar operaciones similares a VLOOKUP usando VBA.

Estructura básica de la función VLOOKUP

Primero, repasemos la función VLOOKUP de Excel.

Sintaxis_de_la_función_VLOOKUP
=VLOOKUP(valor_buscado, matriz_tabla, núm_índice_col, [ordenado])

Por ejemplo, para buscar un valor en la celda A1 dentro del rango B1:C10 y devolver el valor de la 2ª columna (columna C) de esa fila cuando se encuentre:

Ejemplo_de_VLOOKUP
=VLOOKUP(A1, B1:C10, 2, FALSE)

Usando VLOOKUP en VBA

Todas las funciones de Excel se pueden usar en VBA, por lo que puedes llamar a la función VLOOKUP que se ejecuta en Excel desde VBA.

Llamando_VLOOKUP_desde_VBA
Application.WorksheetFunction.VLookup(valor_buscado, matriz_tabla, núm_índice_col, False)

Al llamar a la función Vlookup definida en la clase WorksheetFunction, puedes usar la función VLOOKUP desde VBA.

Sin embargo, WorksheetFunction.VLookup genera un error en tiempo de ejecución (1004) cuando no se encuentra el valor buscado. Para recibir el error como un valor, usa Application.VLookup y comprueba el resultado con IsError.

Detectar_no_encontrado_con_Application.VLookup
Sub SafeVlookup()
    Dim result As Variant
    result = Application.VLookup("lookup", Sheets("Sheet1").Range("B1:C10"), 2, False)

    If IsError(result) Then
        MsgBox "No encontrado."
    Else
        MsgBox result
    End If
End Sub

Si esto es suficiente, no necesitas una función personalizada. Cuando deseas búsquedas más flexibles, crear una función personalizada en VBA es útil. La siguiente sección presenta una función personalizada con una funcionalidad similar a VLOOKUP.

Código para recrear VLOOKUP en VBA

A continuación, te mostraré cómo realizar la misma operación en VBA.

Function CustomVlookup(lookup_value As Variant, table_array As Range, col_index_num As Integer) As Variant
    Dim cell As Range
    For Each cell In table_array.Columns(1).Cells
        If cell.Value = lookup_value Then
            CustomVlookup = cell.Offset(0, col_index_num - 1).Value
            Exit Function
        End If
    Next cell
    CustomVlookup = "Not Found"
End Function

El código anterior define una función personalizada CustomVlookup en VBA que tiene una funcionalidad similar a la función VLOOKUP. Aquí está cómo usarla:

Sub TestCustomVlookup()
    Dim result As Variant
    result = CustomVlookup("ValorBúsqueda", Sheets("Hoja1").Range("B1:C10"), 2)
    MsgBox result
End Sub

Técnicas avanzadas

Hasta aquí Application.VLookup es suficiente, pero VBA permite técnicas como las siguientes.

Búsqueda de coincidencia parcial

Mientras que VLOOKUP estándar solo admite coincidencias exactas, VBA permite búsquedas de coincidencias parciales.

Function CustomVlookupPartial(lookup_value As String, table_array As Range, col_index_num As Integer) As Variant
    Dim cell As Range
    For Each cell In table_array.Columns(1).Cells
        If InStr(1, cell.Value, lookup_value) > 0 Then
            CustomVlookupPartial = cell.Offset(0, col_index_num - 1).Value
            Exit Function
        End If
    Next cell
    CustomVlookupPartial = "Not Found"
End Function

Alternando entre coincidencias exactas y parciales

También es importante poder alternar entre coincidencias exactas y parciales según sea necesario. Por ejemplo, puedes permitir que el usuario especifique esto con un argumento.

Function CustomVlookupFlexible(lookup_value As Variant, table_array As Range, col_index_num As Integer, Optional partial_match As Boolean = False) As Variant
    Dim cell As Range
    For Each cell In table_array.Columns(1).Cells
        If (partial_match And InStr(1, cell.Value, lookup_value) > 0) Or (Not partial_match And cell.Value = lookup_value) Then
            CustomVlookupFlexible = cell.Offset(0, col_index_num - 1).Value
            Exit Function
        End If
    Next cell
    CustomVlookupFlexible = "Not Found"
End Function

Manejo de errores

Aun así pueden producirse errores en tiempo de ejecución, por ejemplo cuando Offset apunta fuera de la hoja o cuando una celda contiene un valor de error como #N/A. Este ejemplo los captura con On Error.

Function CustomVlookupWithErrorHandling(lookup_value As Variant, table_array As Range, col_index_num As Integer) As Variant
    On Error GoTo ErrorHandler
    Dim cell As Range
    For Each cell In table_array.Columns(1).Cells
        If cell.Value = lookup_value Then
            CustomVlookupWithErrorHandling = cell.Offset(0, col_index_num - 1).Value
            Exit Function
        End If
    Next cell
    CustomVlookupWithErrorHandling = "Not Found"
    Exit Function

ErrorHandler:
    CustomVlookupWithErrorHandling = "Error: " & Err.Description
End Function

Ejemplos de uso práctico

Búsqueda en base de datos

Por ejemplo, buscar información de empleados basada en el ID de empleado.

Sub SearchEmployeeInfo()
    Dim employeeID As String
    Dim result As Variant
    employeeID = InputBox("Ingrese_el_ID_del_empleado:")
    result = CustomVlookup(employeeID, Sheets("DatosEmpleados").Range("A1:D100"), 2)

    If result = "Not Found" Then
        MsgBox "Información_del_empleado_no_encontrada."
    Else
        MsgBox "Nombre_del_empleado: " & result
    End If
End Sub

Autocompletar información de productos

Rellenar automáticamente celdas con información cuando se ingresa un código de producto.

Sub AutoFillProductInfo()
    Dim productCode As String
    Dim productName As Variant
    Dim productPrice As Variant

    productCode = Cells(2, 1).Value '_Asume_que_el_código_de_producto_está_en_la_celda_A2
    productName = CustomVlookup(productCode, Sheets("DatosProductos").Range("A1:C100"), 2)
    productPrice = CustomVlookup(productCode, Sheets("DatosProductos").Range("A1:C100"), 3)

    If productName = "Not Found" Or productPrice = "Not Found" Then
        MsgBox "Información_del_producto_no_encontrada."
    Else
        Cells(2, 2).Value = productName '_Ingrese_nombre_del_producto_en_celda_B2
        Cells(2, 3).Value = productPrice '_Ingrese_precio_en_celda_C2
    End If
End Sub

Búsqueda rápida con el método Find

Recorrer las celdas una por una resulta lento con grandes volúmenes de datos. El método Range.Find usa la función de búsqueda de Excel para localizar la celda coincidente de forma eficiente.

Busqueda_exacta_con_Find
Function FindLookup(lookup_value As Variant, table_array As Range, col_index_num As Integer) As Variant
    Dim found As Range
    Set found = table_array.Columns(1).Find(What:=lookup_value, LookAt:=xlWhole, LookIn:=xlValues)

    If found Is Nothing Then
        FindLookup = "Not Found"
    Else
        FindLookup = found.Offset(0, col_index_num - 1).Value
    End If
End Function

Find conserva los valores de LookAt y LookIn de la búsqueda anterior, por lo que debes especificarlos siempre de forma explícita.

チェック

Las versiones más recientes de Excel, como Microsoft 365, ofrecen la función XLOOKUP, y algunas versiones la exponen a VBA como WorksheetFunction.XLookup. Comprueba que esté disponible en tu entorno.

Resumen

Usar VBA permite búsquedas de datos flexibles que no están limitadas por la función VLOOKUP de Excel. Al crear funciones personalizadas combinando estas técnicas, puedes realizar fácilmente procesamientos adaptados a tus necesidades empresariales. Por favor, intenta incorporar estas técnicas en tus propios proyectos.

  • #VBA
  • #Excel
  • #VLOOKUP
  • #Búsqueda de Datos
  • #Función