Professor J
asked on
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.
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
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Change "Error" to CVErr(xlErrNA)
One second difference. :-)
ASKER
:)
thank you very much all of you.
thank you very much all of you.
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.
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.
ASKER
Thank you Rspahits
ASKER
@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?