Home |
Search |
Today's Posts |
#1
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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
![]() |
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Improve Convert function in Excel | Excel Worksheet Functions | |||
Access Module coded converted to Excel Function | Excel Discussion (Misc queries) | |||
Does Excel 2000 have a 'datedif' function to calculate the number. | Excel Worksheet Functions | |||
I cant use englisch function names in a swedich version of excel | Excel Discussion (Misc queries) | |||
Missing function in Excel 2003 | Excel Discussion (Misc queries) |