Indeed the function could do without the xtra columns. But the way it is now
it gives some (no more than that) protection against errors.
But in your example, do you really need to search 65000 rows? By using the
4th argument as false, that may indeed be very time-consuming if you have
values that can not be found.
If you need an exact match but the table is sorted, use true as a fourth
argument and compare the value found yourself. That may be hundreds of times
faster.

Also, you can use the following User Defined Function.
Open the VB editor (ALT+F11), InsertModule, and paste the code in the
module.

' --------------------------------------------------------------------------------------------------------------
Option Explicit

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
' --------------------------------------------------------------------------------------------------------------


--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"Budman" wrote in message
...
When using Vlookup, the function asks for a table array. I'd like to
suggest
a change to the function. Provide a one column range, then allow the
function
to find it ## rows over. For example,

instead of: =Vlookup(A1,C:G,5,false)
simply: =Vlookup(A1,C:C,5,false)

This may seem minor, but when you're doing a lot of reference work in one
spreadsheet, the current function requirements are fairly time consuming.