Solved

help needed with User Define Function Replacing INDEX and MATCH

Posted on 2014-10-10
165 Views
I was looking to replicate and make it much easier to use the Index and Match function to one UDF

although it works, but  it seems to be very slow comparing to the real index and match. also  i could not figure out how to handle the error becuase if the value is not found, then it gives "VALUE" error.  i was wondering if any of you experts have a better idea to share.

thanks.

``````Public Function II_WAY_LOOKUP(Lookup_Value As Variant, Reference_Column As Range, Result_Column As Range)
II_WAY_LOOKUP = WorksheetFunction.Index(Result_Column, WorksheetFunction.Match(Lookup_Value, Reference_Column, 0), 1)
End Function
``````
0
Question by:ProfessorJimJam
• 3
• 3
• 2
• +1

LVL 22

Assisted Solution

rspahitz earned 100 total points
You can improve this function.  For one thing, you should have a return data type.

If you use some of the native VB code, it could also be faster.

Since VB doesn't have native routines to search Excel, you either have it tell Excel how to do it (as you have) or let VB talk to Excel in different ways, such as check the value of each cell for the thing you want then return an appropriate value such as this:

``````Public Function II_WAY_LOOKUP(Lookup_Value As Variant, Reference_Column As Range, Result_Column As Range)
' II_WAY_LOOKUP = WorksheetFunction.Index(Result_Column, WorksheetFunction.Match(Lookup_Value, Reference_Column, 0), 1)
Dim row as Long
Dim cellValue as String
row=1
Do
cellValue = Cells(row, 1).Value
If cellValue = "" Then
Exit Do
End If
' additional matching criteria on cellValue
row = row+1
Loop
End Function
``````

would you like to continue this direction?
0

LVL 24

Accepted Solution

Phillip Burton earned 300 total points
UDFs are slow compared to Excel functions - there's no real way around that. That's why you should avoid them unless you can't. They can be re-written, but they will never be as fast as Excel functions. They may also be re-calculated at every single update.

Regarding the error, add a new line 2

``````On Error goto IIErr
``````

Additional lines before the End Function line

``````Exit Function
IIErr:
II_Way_Lookup="Error"
``````
0

LVL 25

Author Comment

@rspahitz   thanks. i used your code, and it gave me circular references error. so it did not work like the original code.

@Phillip   thanks.  on error option gives me the "Error" string , however i was wondering if it could return the standard #N/A error, like it normally does in Index and Match. would this be possible?
0

LVL 85

Assisted Solution

Rory Archibald earned 100 total points
Yes - use:

``````II_Way_Lookup= CVerr(xlerrna)
``````
0

LVL 24

Expert Comment

Change "Error" to CVErr(xlErrNA)
0

LVL 24

Expert Comment

One second difference. :-)
0

LVL 25

Author Comment

:)

thank you very much all of you.
0

LVL 22

Expert Comment

JimJam, "it gave me circular references error"
It wasn't intended as a completed function (see line 11)...I just didn't want to add more (since I only had a few minutes to write it) if you wanted to avoid going that direction.
0

LVL 25

Author Comment

Thank you Rspahits
0

Featured Post

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…