Excel 2007: VBA function faster than Match function

Please describe exactly what the function needs to do.

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?

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.

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.