Link to home
Start Free TrialLog in
Avatar of ouestque
ouestqueFlag for United States of America

asked on

Excel 2007: VBA function faster than Match function

What is the VBA for a function that is faster than the Match function?
Avatar of Martin Liss
Martin Liss
Flag of United States of America image

Please describe exactly what the function needs to do.
Avatar of Norie
Norie

I don't think there is a function that's faster than MATCH, in fact a combination of INDEX/MATCH is often used instead of VLOOKUP.

What exactly are you doing?
Avatar of ouestque

ASKER

Hey guys. I  am trying to create a User Defined Function that is faster than Match(). Is that possible?
So the UDF would need to basically do what the Match function does, but faster. Find the row number of a value in a range.
Using a UDF instead of native Excel functionality is more than likely going to be slower.
You can do this.  However, you need to understand that the speed will be offset by any update activity to the range you are matching against.  Before we begin, a few questions.

1. How static is the match range?
2. How big is the match range?
3. What is the data type of the cell values in the match range?  (numbers, dates, string, other, mixed)
4. Do you need the exact functionality of the Match() function or just searching for exact matches?
5. Is your current match range sorted?  If not, you should test the Match() function performance with sorted data.
1. Match range on most sheets do not change. Nonetheless, on some sheets new records are added every week.
2. 50k rows.
3. String.
4. Just searching for exact matches.
5. Yes.
ASKER CERTIFIED SOLUTION
Avatar of aikimark
aikimark
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
If we need to simulate the Match() function more exactly, the FasterMatch() function should probably return #N/A error instead of -1 in the not-found scenario.
Awesome solution aikimark!! Thank You!!
It is possible to expand this solution to multiple ranges you want to do FasterMatch() upon.