Finds first occurrence of item in column, then returns value from same row as of columnoffset, logical vlookup VBA equivalent.
Image credit
https://www.flickr.com/photos/bdunnette/359763780/in/
Function ANmaVLookup(ForValue, Optional InColumn = "A", Optional InSheet = "This", Optional InWB = "This", Optional ReturnColumnOffset = 1)
' Does VLookup in VBA, easier parameters
' Finds first occurrence of item in column, then returns value from same row as of columnoffset
' ColumnOffset can be +ve of -ve
'
' Needs
Rett = ""
If InWB = "This" Then InWB = ThisWorkbook.Name
If InSheet = "This" Then InSheet = Workbooks(InWB).ActiveSheet.Name
RowMat = MatchIf(ForValue, InColumn, InWB, InSheet)
If RowMat > 0 Then Rett = Workbooks(InWB).Worksheets(InSheet).Range(InColumn & RowMat).Offset(, ReturnColumnOffset).Value
ANmaVLookup = Rett
End Function
' Does VLookup in VBA, easier parameters
' Finds first occurrence of item in column, then returns value from same row as of columnoffset
' ColumnOffset can be +ve of -ve
'
' Needs
Rett = ""
If InWB = "This" Then InWB = ThisWorkbook.Name
If InSheet = "This" Then InSheet = Workbooks(InWB).ActiveSheet.Name
RowMat = MatchIf(ForValue, InColumn, InWB, InSheet)
If RowMat > 0 Then Rett = Workbooks(InWB).Worksheets(InSheet).Range(InColumn & RowMat).Offset(, ReturnColumnOffset).Value
ANmaVLookup = Rett
End Function
ForValue, Optional InColumn = "A", Optional InSheet = "This", Optional InWB = "This", Optional ReturnColumnOffset = 1
Views 90
Downloads 48
CodeID
DB ID