Reverse MATCH

Hi

I was looking at a closed thread called:
Explain =LOOKUP(2,1/(A2:A10=D2),B2:B10)

I thought I'd add some information from a bit of research I've done.

I'm using Excel 2003. I've tried later versions but prefer to continue with this one.

I use the MATCH() worksheet function a lot, often combined with INDEX(), and for various projects I found I needed a similar function to match the last instance of a value rather than the first.

Initially, I wrote a small and fairly simple UDF which I called RevMATCH() and this worked pretty well. Code as follows:

Please Login or Register  to view this content.

But I want to avoid UDFs wherever possible, since they tend to operate very slowly compared to worksheet functions. Also they make your workbooks less portable.

So, with this in mind, I thought that one solution might be to create a reversed range somewhere on the sheet in question and then just use MATCH() on it. The "upside-down" range can then be hidden away.

This worked OK but became a bit cumbersome when the ranges being worked upon were large.

I found two other solutions, one using MATCH() and MAX() in an array formula, but I'm not a big fan of array formulas. Another uses MATCH() and SUMPRODUCT(). Both of these worked OK but ended up with very long multi-line formulas.

Finally (perhaps) I found this thread, and using LOOKUP() with vectors seems to solve the problem. The formula is still quite long but is a bit shorter than the others.

I've attached a little workbook that tests all these solutions. It is, I think, self explanatory. I hope I've managed to attach it properly.

Can anyone think of any better solution?


Alan

Hello Alan_42. Welcome to the forum.

Thank you for the tip.

One thing more.

Please read the forum rule RE use of code tags and make the necessary corrections to your post.

Administrative Note:

Welcome to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)