Possible to make a UDF for the INDEX MATCH combo function?

There have been several articles written by various authors regarding the advantages of using the INDEX/MATCH formula over the related VLOOKUP function. However, probably the biggest obstacle that exists for those interested in just making an absolution switch over to INDEX/MATCH (including myself) is the complexity of the function needed using both INDEX and MATCH and the lack of the same intuitive variable usage in the required INDEX/MATCH functions that exists in VLOOKUP, which gets me to my request.

Q: Does there exist, or can VBA code be written, that would make a new UDF that simplifies the use of the INDEX/MATCH function?

I keep this little two line cheat summary nearby for using INDEX/MATCH, including the MSDN reference when needed:

=INDEX ( Column I want a return value from , MATCH ( My Lookup Value , Column I want to Lookup against , Enter “0” ))

Additional use of this: https://msdn.microsoft.com/en-us/lib...ffice.14).aspx

My suggestion would be to create a UDF, say “INMATCH” (INdex MATCH) that would work like this:

INMATCH (lookup value, column value from, column lookup against, [match type, default = “0” is exact match])

Adding this to your personal VBA project modules for global usage on a user’s PC would be killer!!

Really looking for some feedback/help. Thanks!!!

I have previously tried, but was unsuccessful in creating the UDF.

Do you think you could quickly create a working INDEX/MATCH UDF?

Thanks!!

I appreciate the feedback, but writing is the UDF is the solution I'm looking for.

Do you have the ability to take an initial attempt to write it? as I mentioned, I have not been successful. Thanks.

As a start...

Please Login or Register  to view this content.

This could do with further development to tighten up input parameters, output etc. Others here may have suggestions on using alternatives to the WorksheetFunctions, which I suspect aren't that fast.

It would also need to be included in an AddIn workbook, rather than Personal.xlsb, if it is to be used in other workbooks.

If performance is an issue, then an in-cell INDEX+MATCH would be quicker (this UDF took twice as long as INDEX+MATCH on a 200k row sample set).