(Note: It is not advisable to use this for large amount of rows or data. Instead, use Vlookup or IndexMatch for larger data.)
Introducing the upgrade version of Vlookup and IndexMatch Function when it comes to flexibility and looking up data using VBA Macros Add-In customized and defined. It is more likely intelligent and flexible compare with these two functions mentioned. This powerful tool will help you find your item then match it with your desired output. Unlike Vlookup and IndexMatch, MATCHME lookup formula would help you find in easier way, only just a few parameters would be used. The logic of this formula is the same with IndexMatch function because MatchMe function also uses wildcard “*” for substring searching (approximate match) but MatchMe is far more easier to use than IndexMatch. The disadvantage of this lookup formula has a slow process of computing, Vlookup and IndexMatch have greater advantage when it comes to computing or calculating.
Example # 1
Example # 3
Here are the Parameters of MATCHME function:
=MATCHME(Item,ItemRange,OutputRange,Condition)
Item – The subject or item that will find to Item Range.
ItemRange – An array or specific column you want to search with your Item.
OutputRange – The outcome or result of matching.
Condition – A boolean data type, meaning only TRUE or FALSE are given. When it’s TRUE, Finding will be as Whole value, i.e., Exact Match. While FALSE, Finding will be as by Part. Users gives flexibility with this type of boolean, like example above.
If you want to show the tooltip, press CTRL-SHIFT-A when using this formula.
HOW TO ADD A FORMULA TO YOUR EXCEL PROGRAM
- Download the add-in file from my mediafire or google account (It’s free)
- http://www.mediafire.com/file/hsvnhbkaaicc0d1/MatchMe.xlam or
- https://drive.google.com/open?id=0B_gWAZ4O0Nh8eW5wU1EyV1RHWlk
- Open Excel program -> click File – Options – Add-Ins – Manage:Excel Add-Ins Go – Browse (browse the downloaded file)
code:
Option Explicit
Function MATCHME(Item As Variant, ItemRange As Range, OutputRange As Range, Condition As Boolean)
‘————————————-
‘Created By: Edvinne M. Capistrano
‘Email: edvinne123@gmail.com
‘Website: http://www.excel-crack.com
‘————————————-
Dim wbNme, wsNme, RngNme As String
wbNme = ItemRange.Parent.Parent.Name
wsNme = ItemRange.Worksheet.Name
With Workbooks(wbNme).Worksheets(wsNme)
If Condition = True Then
MATCHME = WorksheetFunction.Index(.Range(OutputRange.Address), _
WorksheetFunction.Match(Item, .Range(ItemRange.Address), 0))
ElseIf Condition = False Then
MATCHME = WorksheetFunction.Index(.Range(OutputRange.Address), _
WorksheetFunction.Match(“*” & Item & “*”, .Range(ItemRange.Address), 0))
End If
End With
End Function
Download Link:
MatchMe.xlam