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

[code language=”vb”]
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
[/code]

Example: