MLOOKUP() - the Easy Mult-Key INDEX()/MATCH()
Post date: Dec 11, 2017 9:51:40 PM
Searching Excel tables on multiple criteria is a pain. So I created MLOOKUP() and XLOOKUP(), fast and easy to use VBA functions that also works well as UDFs (User Defined Functions).
The difference between MLOOKUP and XLOOKUP is that XLOOKUP, like VLOOKUP, requires key columns be the left most columns of our data and MLOOKUP() allows key columns to be anywhere within our data and in any order.
Click XLOOKUP() for more on that routine.
Syntax:
=MLOOKUP([Table],
[Return Column],
[[Lookup Key1],[Lookup Value1],
[Lookup Key2],[Lookup Value2]
... ])
Example:
=MLOOKUP(MyTable,"MyField", 5, "MatchThis", 3, "MatchThisToo")
This returns the value in MyTable[MyField] where MyTable's fifth column matches "MatchThis" and third column matches "MatchThisToo".
Parameters:
Notes
MLOOKUP() only works with tables because tables are awesome and no one with XL 2007 or later should be making lists that aren't tables.
Can't we do the same thing with INDEX()/MATCH()?
Yes, if we concatenate keys and columns in an array formula which is how this works. The major difference is ease of use. MLOOKUP() is much easier to setup and read/understand.
If INDEX/MATCH can do the same thing, why go to so much effort to create this routine?
Because looking up data in tables is something we do very often so I wanted something that is as easy to setup as possible, as easy to read and maintain as possible, and fast.
Summary:
MLOOKUP() is amazingly easy to use and extremely fast. I built it primarily to simplify common VBA tasks but it also works extremely well as a UDF. So if your project requires VBA, you might as well use MLOOKUP() because it documents formulas better than VLOOKUP and is less complex than INDEX()/MATCH() combinations.
Demo Download:
The code below may not be the most up-to-date. The most up-to-date version can be downloaded from here:
https://www.dropbox.com/s/05y59c72774smyd/XLookup.xlsm?dl=0
Code:
WARNING! Do not just copy and paste this code. It will not compile as is. The code below is from module modGeneral which includes required module level constants, module level options, and the DspErrMsg function. It is shown here in isolation for discussion purposes. See the Demo Download section (just above this) for how to get this routine in proper context.
NOTE! Most of this code is comments, making sure inputs are correct and reporting errors. This is particularly important with UDFs.
Public Function MLookup(ByVal vTable As Variant, _
ByVal vResult As Variant, _
ParamArray vKVP() As Variant) As Variant
' Description:Multiple Key (any order) lookup
' Inputs: vTable Data table
' vResult Result Column (heading or #)
' vKVP Key/Value pair(s) to search for
' Outputs: Me Success: Cell
' Failure: If UDF #REF Else Nothing
' Requisites: *None
' Example: ?MLookup("tblCities","Population","Country","USA","State","VA","City","Norfolk")
' Date Ini Modification
' 12/11/17 CWH Initial Development
' Declarations
Const cRoutine As String = "MLookup"
Dim oLo As ListObject 'Table containing data
Dim oLC As ListColumn 'Table column
Dim vKeys As Variant 'vKVP internal version
Dim sCol As String 'Column Address Range to search
Dim vKey As Variant 'Key(s) to find in Column(s)
Dim lKey As Long 'Current key
Dim lRow As Long 'Found Row
Dim lCol As Long 'Found Column
Dim sAddTxt As String 'Additional Error Text
' Error Handling Initialization
On Error GoTo ErrHandler
Set MLookup = Nothing
' Check Inputs and Requisites
' Table
Select Case TypeName(vTable)
Case Is = "ListObject": Set oLo = vTable
Case Is = "Range": Set oLo = vTable.ListObject
Case Else: Set oLo = ActiveSheet.Evaluate(vTable).ListObject
End Select
' Return Column
If TypeName(vResult) = "Range" Then vResult = vResult.Value2
' Search Keys
If UBound(vKVP) = -1 Then Err.Raise DspError, , "#Key(s) required"
' When called by VBA, ParamArrays sometimes are stuffed in the first element
If IsArray(vKVP(LBound(vKVP))) Then _
vKeys = vKVP(LBound(vKVP)) Else _
vKeys = vKVP
' Procedure
With oLO
If Not oLo.DataBodyRange Is Nothing Then
' Just 1 key - Use Worksheet.Function because it is fastest w/1 Key
If UBound(vKeys) <= 2 Then
vCol = vKeys(LBound(vKeys))
vKey = vKeys(UBound(vKeys))
If IsNumeric(vKey) Then vKey = CDbl(vKey)
lRow = Application.WorksheetFunction.Match( _
vKey, _
.ListColumns(vCol).DataBodyRange, _
0)
' More than 1 key - Use Worksheet.Evaluation because it is fastest w/multiple keys
Else
' Concatenate Keys and Search Columns
For lKey = LBound(vKeys) To UBound(vKeys) Step 2
' Determine column name
If TypeName(vKeys(lKey)) = "Range" Then _
Set oLC = oLo.ListColumns(vKeys(lKey).Value2) Else _
Set oLC = oLo.ListColumns(vKeys(lKey))
' Combine Column Addresses
sCol = IIf(sCol <> vbNullString, sCol & " & ", vbNullString) & _
oLC.DataBodyRange.Address
' Determine Key Value
If TypeName(vKeys(lKey + 1)) = "Range" Then _
vKey = vKey & vKeys(lKey + 1).Value2 Else _
If IsDate(vKeys(lKey + 1)) Then _
vKey = vKey & CLng(vKeys(lKey + 1)) Else _
vKey = vKey & vKeys(lKey + 1)
Next
' Find Row # by Evaluating MATCH within the Table's worksheet
lRow = .Parent.Evaluate("=Match(""" & vKey & """," & sCol & ",0)")
End If
' Get Column #
lCol = oLo.ListColumns(vResult).Index
' Return result
Set MLookup = oLo.ListRows(lRow).Range(lCol)
End If
ErrHandler:
If Err.Number > 0 Then
' Create Additional Error Text (if needed)
Select Case Err.Number
Case Is = 9: If lKey > UBound(vKeys) Then _
sAddTxt = vResult Else _
sAddTxt = vKeys(lKey)
sAddTxt = "Column " & sAddTxt & " not found in " & oLo.Name
Case Is = 13, 1004: sAddTxt = Join(vKeys, ",") & " not found in " & oLO.Name
Case Is = 424: sAddTxt = "Table not found"
End Select
' Customize Errors based on UDF of VBA caller
If TypeName(Application.Caller) = "Range" Then 'Called from UDF
MLookup = CVErr(xlErrRef) 'Raise #REF and log error
Debug.Print cRoutine & ":" & Err.Description & vbLf & sAddTxt
Else 'Called from VBA (most likely)
Select Case Err.Number
Case Is = 13, 1004: 'Key(s) not found. Log Error
Debug.Print cRoutine & Err.Description & vbLf & sAddTxt
Case Else:
Select Case DspErrMsg(cModule & "." & cRoutine, sAddTxt)
Case Is = vbAbort: Stop: Resume 'Debug mode - Trace
Case Is = vbRetry: Resume 'Try again
Case Is = vbIgnore: 'End routine
End Select
End Select
End If
End If
End Function
Learning Opportunities
Some of the techniques used in this routine are above the beginner level. If that's you, know that everyone starts as beginners and that a good way to advance is to study more advanced code. Below are some areas that I believe most beginners will have difficulty understanding.
Unlimited Parameters
The ability to handle as many table keys as needed is what sets this routine apart. ParamArray vKVP() makes this possible. ParamArray instructs VBA to place all parameters from this point and forward into an array and pass the array to our routine. When using ParamArray we must be aware of a few things. ParamArray:
Must always be the last parameter in the parameter list
Can have all of its values stuck into its first array element when call by VBA. If this happens in MLOOKUP(), vKVP(0) contains the array it needs.
A difference between MLookup and XLookup is MLookup lets us search any table column and as many table columns as we want. So ParamArray must contain both the column to search and the key value to search for. These are called "Key Value Pairs" or KVP. This means we must read 2 array elements for each KVP (step through ParamArray by 2's).
For lKey = LBound(vKeys) To UBound(vKeys) Step 2
Arrays
VBA's arrays normally start with 0. Exceptions include when arrays are created from ranges or if the module where the array is created has Option Base 1 at its top. For this reason it is a safe practice to use LBOUND() and UBOUND() to determine the array's lowest and upper dimension values.
NOTE! If LBOUND() of an array variable is -1 the array variable has not be set. See Err.Raise below.
Dates
Dates are tricky. Excel stores dates as single numbers (ex 42767) also known as Microsoft Excel date-time code. As you know Excel often displays dates with separate year, month, and day values (ex. 2/1/2017) and dates can be displayed in various formats. Those formatted values are kept in the cell's VALUE and TEXT properties. The single number value is in the cell's VALUE2 property. Excel's MATCH() function looks at each cell's VALUE2 property so date lookup parameters must be the single number version. For this reason MLOOKUP() uses cell parameters' VALUE2 for all range values.
Err.Raise:
BXL routines have a section labeled Check Inputs and Requites. This section makes sure parameters are passed correctly and requisites are in place before processing begins. Incorrect parameters and/or missing requisites are not VBA errors but they are errors to the routine. We can let VBA handle these errors like VBA errors using the Raise method of the Err object via Err.Raise.
Handling Errors:
If it were not for the possibility of errors this routine would just be a dozen lines. MLOOKUP() uses BXL's standard error handling routines. So if you are using BXL's DspErrMsg() and module naming standards this will work as is. If not, you must:
Replace DspErrMsg() with your own routine and its module level constants (DspError).
Either remove all references to cModule or define that in the routine or in your module's header.
We should handle errors differently depending on if we call MLOOKUP() from a spreadsheet formula or from VBA. We can determine which is the case by examining TypeName(Application.Caller). If it equals "Range", MLOOKUP() was called by a UDF as part of an Excel formula; otherwise, it was called by a VBA routine.
If we call MLOOKUP() from the spreadsheet we must return one of Excel's #ERROR values. In this case MLOOKUP() returns #REF if one of the parameters is wrong by setting MLookup = CVErr(xlErrRef).
If we call MLOOKUP() from a VBA routine MLOOKUP():
Returns results as objects or Nothing so we can always SET our result variables.
Reports errors in message boxes.
Speed:
This routine is designed primarily to simplify finding values using multiple keys. Where performance is critical and VLOOKUP works (single key in left most column) use VLOOKUP because VLOOKUP is by far quicker. But for multiple key lookups, over larger tables this is very nearly as fast as it gets using VBA. Below are the results recorded from modCustom.Test in the demo download.
Time comparison in milliseconds of multiple key lookups over table CDC - 10,000 iterations.
Keywords:
Free, Excel, XL, Tutorial, How To, Errors, UDF, User Defined Function, VBA, VLOOKUP, INDEX, MATCH, Download, Craig Hatmaker, BXL, Beyond Excel