NthMatch UDF as An Alternative to Array function...

Hi Folks,
Yesterday I posted following thread in this section ..
http://www.excelforum.com/tips-and-t...ift-enter.html
The thread was about using "Sumproduct as an alternative to array formulas.."
I was very happy that I made something like this..

but our Sir Mr. XOR opened my eyes and explain me why my formula was not that much fast as array function.. and was more intensive resource wise as well...

Then I started think about another alternative..
Actually I had, before posting the above thread, made an UDF ie NthMatch.. to return the Row No. of the Nth Match of a value in the array..
It is an improvement over Match function.. as Match function only returns row No. of the first Match...

when I got the solution.. what I saw that The NthMatch alternative was short(lenght wise) of the Sumproduct and Array alternatives..
So that is what I wanted to share with you all..
Have a visit at the link below.. and let me know whether there are any chances of improvement..
http://excel-buzz.blogspot.in/2014/0...-to-array.html

Here is the code for NthMatch(Lookup_value, Lookup_Range, Match No.)

Please Login or Register  to view this content.

View post #9 for better solution given by KVS

Regards,
Vikas Gautam

Improved A bit..

Now It jump over the result when Match No. matches the row no....
means It stops looping when Nth Match is found.. and throws the result...

Here is the code..

Please Login or Register  to view this content.

If there are chances of improvement then don't hesitate.. Have a comment..

My main criticism is that your use of the For each ... next loop will cause the code to search each and every entry for a match. It can be an effective "brute force" algorithm, and may be necessary for some situations. I would consider including, similar to Excel's built in lookup functions, a "range_lookup" argument. If the user will provide a sorted list -- then you can use a much more efficient binary search algorithm (http://en.wikipedia.org/wiki/Binary_search_algorithm ). You also only need to find the first and last match -- recognizing that, if the list is properly sorted, all matches will be in between the first and last match. I could see potential for significant performance improvement if you allow for (or even encourage) the search on a sorted list.

Gregor Y,
I really appreciate the work you have done..
But I think you have not seen one given by KVS.. ies

Please Login or Register  to view this content.

The above code is for RANGE and ARRAY as well..
It uses fantastic string manipulation.. very very fast..
Try it for your performance test
here is one more link for performance appraisals..
Improving performance in excel
There are some codes for speed testing, must try..

Good Luck..

Thanks for the suggestion Gregor y
I guess you got what you were looking for..

Hi KVS and all Experts.
Have a look, NthMatch Redefined

Please Login or Register  to view this content.

Note:- The file doen't contain the same code. So Copy and paste from here.
Check the attached file:-

Here is another new way,
may be I have reinvented.
This time NthMatchM with same set of parameters.

Please Login or Register  to view this content.

Note:- I have tested the speed of this code, and its much slower than above one.

Pl see the file.
Try this code also for speed test.

Please Login or Register  to view this content.

Hi KVS Sir,
I acknowledge your code.
But I tested both and Mine is 3 secs faster than yours. ( with due respect )
your was 9 secs.
mine was 6 sec
I have run both five times.
The Speed may vary PC to PC.
I am attaching the file having both the Codes.
In sheet 2 I have arranged the test.
2500 rows of data.
Just select the formula range and run the RangeTimer code in the macro.
May be you have other ideas so test in your way.

Hi vikas
I have tested and found mine was slow. Now I have changed code.Pl check with attached file.

Hi KVS Sir,
There is a problem with your code (When I removed last 500 rows of data from reference),
Pl have a look at Row 670 for explanation.

I acknowledge the fact that you have the speed a lot by removing unnecessary steps in the code.
So, I believe the Final version should be.

Please Login or Register  to view this content.

Hi KVS Sir,
There is a problem with your code (When I removed last 500 rows of data from reference),
Pl have a look at Row 670 for explanation.

I acknowledge the fact that you have improved the speed a lot by removing unnecessary steps in the code.
So, I believe the Final version should be.

Please Login or Register  to view this content.

Yes, KVS Sir,
Your is faster but I didn't understand how.
However, I have come up with one more technique.
Here is it.

Please Login or Register  to view this content.

Because when I tried the array construction, it was faster than every other technique.
So I tried to copy the same behavior using Evaluate Function.
Yours took 4.15 secs but it takes only 2 sec. (would be more fast on your PC)
Try it and pls share the results.

Here is the Code with less words but matches ur speed:-

Please Login or Register  to view this content.

Yes I agree sir.
But I have learnt a lot from what we have done so far.
Thanks for your cooperation.