ouestque

asked on

# Excel 2007: VBA function faster than Match function

What is the VBA for a function that is faster than the 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?

What exactly are you doing?

ASKER

Hey guys. I am trying to create a User Defined Function that is faster than Match(). Is that possible?

ASKER

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. 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.

ASKER

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.

2. 50k rows.

3. String.

4. Just searching for exact matches.

5. Yes.

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

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.ASKER

Awesome solution aikimark!! Thank You!!

It is possible to expand this solution to multiple ranges you want to do FasterMatch() upon.