Excel function

M

mark3947

I would like to create my own worksheet function to do a vlookup. Does
someone have an example that will work?
 
N

Niek Otten

Don't know what you want to do, but here is one example:

' =================================
Function VLookupSort(SearchArgument As Range, SearchTable As Range, _
ColumnNo As Long, Optional SortDirection, Optional NotFound)
' Works as Vlookup, exact match (4th argument = FALSE)
' But takes advantage of the fact that a table is sorted
' and thus is much faster
' Also permits table to be sorted descending (Sortdirection -1)
' Optional argument for return value if item not found, defaults to #NA
Dim ItemFound

If IsMissing(SortDirection) Then SortDirection = 1

ItemFound = Application.Match(SearchArgument, Intersect(SearchTable,
SearchTable.Cells(1).EntireColumn), _
SortDirection)
If SearchTable(ItemFound, 1) <> SearchArgument Then
If IsMissing(NotFound) Then
VLookupSort = CVErr(xlErrNA)
Else
VLookupSort = NotFound
End If
Else
VLookupSort = _
SearchTable(ItemFound, ColumnNo)
End If
End Function
' =================================
 
E

Eduardo

Hi,
Let's say that in Sheet2 you have your data i.e.
George 100
Alan 200
Names are in column A and values in Column B
so in sheet 1 the idea is once entered George in A1 in B1 will retrieve the
value 200
Formula to enter in B1 is

=+VLOOKUP(A3,Sheet2!A:B,2,FALSE)

Where 2 is the column where you need to retrieve the data
hope this help if yes please said yes
 
Top