help needed with User Define Function Replacing INDEX and MATCH

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

LVL 27
ProfessorJimJamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rspahitzCommented:
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
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ProfessorJimJamAuthor Commented:
@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
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Rory ArchibaldCommented:
Yes - use:

II_Way_Lookup= CVerr(xlerrna)

Open in new window

0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Change "Error" to CVErr(xlErrNA)
0
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
One second difference. :-)
0
ProfessorJimJamAuthor Commented:
:)

thank you very much all of you.
0
rspahitzCommented:
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
ProfessorJimJamAuthor Commented:
Thank you Rspahits
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.