Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Budman
 
Posts: n/a
Default Simplify Vlookup function in Excel

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.
  #2   Report Post  
Niek Otten
 
Posts: n/a
Default

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.



  #3   Report Post  
Duke Carey
 
Posts: n/a
Default

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.

"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.



  #4   Report Post  
JulieD
 
Posts: n/a
Default

Hi

if you'ld like to let MS know what you think email them at


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

"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.



  #5   Report Post  
Budman
 
Posts: n/a
Default

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


  #6   Report Post  
JulieD
 
Posts: n/a
Default

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

"Budman" wrote in message
...
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



  #7   Report Post  
Niek Otten
 
Posts: n/a
Default

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

"Budman" wrote in message
...
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



  #8   Report Post  
Ernst Guckel
 
Posts: n/a
Default

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.


"Budman" wrote:

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.

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Improve Convert function in Excel Mike VV Excel Worksheet Functions 0 February 3rd 05 05:45 AM
Access Module coded converted to Excel Function Adam Excel Discussion (Misc queries) 1 December 23rd 04 03:48 PM
Does Excel 2000 have a 'datedif' function to calculate the number. Kaddy Excel Worksheet Functions 7 December 11th 04 09:53 PM
I cant use englisch function names in a swedich version of excel PE Excel Discussion (Misc queries) 2 December 7th 04 02:00 AM
Missing function in Excel 2003 Galldrian Excel Discussion (Misc queries) 2 November 30th 04 01:34 PM


All times are GMT +1. The time now is 04:27 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"