Simplify Vlookup function in Excel

[Guest]

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.

 

[Niek Otten]

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), Insert>Module, 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

 

[Duke Carey]

In addition to Niek's comments about limiting the size of your lookup range,
I'd encourage you to use a named range for that lookup range. It just
simplifies the task of creating new VLOOKUP-based formulas, as you can
quickly insert the range name for the lookup array.

 

[JulieD]

Hi

if you'ld like to let MS know what you think email them at
(e-mail address removed)

with Excel in the subject line and your ideas / reasons in the message body.

BTW i never use full columns in my VLOOKUP ranges, more often than not i'll
use a dynamic range name for the table array - then i don't have to redefine
the number of columns / rows - it can do it itself.
Check out www.contextures.com/tiptech.html on how to create dynamic range
names if this is new to you

Cheers
JulieD

 

[Guest]

I don't disagree with any of you, there's certainly other ways to do lookups.
But it doesn't change my original comment....why require a table array? I
just don't think it belongs in the function.

Budman

 

[JulieD]

Hi

when it comes to MS i gave up asking "why" a long time ago ... as previously
suggested, email the suggestion to MS, you never know, they might act on
it..

Cheers
JulieD

 

[Niek Otten]

Theoretically it does. Programming (and math) purists would tell you that a
function is not allowed to access anything that is not passed to the
function via arguments in the function call. And there are very good reasons
for requiring this.

I agree that is not of much use to you, as a user. But I don't see the
problem either.

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel

 

[Guest]

Why not try this instead of Vlookup:

=OFFSET(A1,MATCH(E2,A:A,0)-1,E3)

Where your lookup data is in col A, E2 is the lookup value and E3 is the
column the return value is in. The data table can go as wide as you like
without redifining the formula.

Ernst.