VBA – Excel – Lookup Range

Function to return a value for a specific id from a dataset with column headers in the first row (ie a standard dataset in Excel).

The function requires four parameters:

1. Range to look at
2. ID column name
3. ID to look for
4. Column name containing the value to be returned

Function LU_Range(InRange As Range, InLU_Value As Variant, InLU_Col As Variant, InRetValueCol As Variant) As Variant
    ' With the specified range, the LU_Value is searched for in the LU_Col to determine the row to use and then the value from
    '   the Return Value Column variable is returned using the index function
    
    Dim LUCol As Range, RetValueCol As Range
    
    
    Set LUCol = InRange.Columns(Application.WorksheetFunction.Match(InLU_Col, InRange.Rows(1), 0))
    Set RetValueCol = InRange.Columns(Application.WorksheetFunction.Match(InRetValueCol, InRange.Rows(1), 0))
    
    
    'Index function is used with the 'return value column' with the position coming from using match function on the 'lookup column'
    LU_Range = Application.WorksheetFunction.Index(RetValueCol, Application.WorksheetFunction.Match(InLU_Value, LUCol, 0))
    
End Function

Example: