Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

help needed with User Define Function Replacing INDEX and MATCH

Posted on 2014-10-10
9
Medium Priority
?
186 Views
Last Modified: 2014-10-10
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

Open in new window

0
Comment
Question by:ProfessorJimJam
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 22

Assisted Solution

by:rspahitz
rspahitz earned 400 total points
ID: 40373090
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

Open in new window


would you like to continue this direction?
0
 
LVL 24

Accepted Solution

by:
Phillip Burton earned 1200 total points
ID: 40373095
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

Open in new window


Additional lines before the End Function line

Exit Function
IIErr:
II_Way_Lookup="Error"

Open in new window

0
 
LVL 27

Author Comment

by:ProfessorJimJam
ID: 40373147
@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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 400 total points
ID: 40373158
Yes - use:

II_Way_Lookup= CVerr(xlerrna)

Open in new window

0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40373159
Change "Error" to CVErr(xlErrNA)
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40373162
One second difference. :-)
0
 
LVL 27

Author Comment

by:ProfessorJimJam
ID: 40373170
:)

thank you very much all of you.
0
 
LVL 22

Expert Comment

by:rspahitz
ID: 40373330
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 27

Author Comment

by:ProfessorJimJam
ID: 40373352
Thank you Rspahits
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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.
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa‚Ķ

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question