XLOOKUP() - the Easy Multi-Key VLOOKUP()
Post date: Dec 8, 2017 2:55:25 PM
Searching Excel tables on multiple criteria is a pain. So I created XLOOKUP(), a fast and easy to use VBA function that also works well as a UDF (User Defined Function). Syntax:
=XLOOKUP([Table],
[Return Column],
[[Lookup Key 1] [Lookup Key 2] ...])
Example:
=XLOOKUP(MyTable,"MyField","MatchThis","MatchThisToo")
This returns the value in MyTable[MyField] where MyTable's first column matches "MatchThis" and the second column matches "MatchThisToo".
Parameters:
NOTE!
This was originally posted Dec 8, 2017. On Aug 29, 2019 Microsoft introduced it's own XLOOKUP(). Excel's new XLOOKUP shares the same name and includes this functionality, but, I'm sure it's coincidence.
Or is it?
Table
Return Column
Lookup Key(s)
Can be a table name (ex: "MyTable") or a table references (ex: MyTable) or any reference inside the table (ex: $A$1, MyTable[MyField]). I do not recommend using cell references because formulas can break if the table is moved.
Can be either a table column # (ex: 2) or a column heading ("MyField"). I recommend headings because they help self-document formulas and formulas won't break if columns are rearranged.
Can be one or more values to search for. The first value must be located in the first column. The second value must be in the second column and so forth.
Notes
XLOOKUP() only works with tables because tables are awesome and no one with XL 2007 or later should be making lists that aren't tables.
This only works with keys located in the left most columns (like VLOOKUP) because unique identifiers should always be on the left. BUT! If you need to match columns that could be anywhere in a table and in any order, see MLOOKUP()
Can't we do the same thing with VLOOKUP()?
Not without adding helper columns to data (see: https://exceljet.net/formula/vlookup-with-multiple-critiera).
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. XLOOKUP() 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:
XLOOKUP() is amazingly easy to use and fast. I built it primarily to simplify common VBA tasks but it also works well as a UDF. So if our project requires VBA we could use XLOOKUP() because its column heading parameters document formulas better than VLOOKUP's column number, and XLOOKUP is far less complex than INDEX()/MATCH() combinations as long as our keys are in the left most columns. And if our keys are not in the left most column, see MLOOKUP().
Demo Download:
This code 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 XLookup(ByVal vTable As Variant, _
ByVal vResult As Variant, _
ParamArray vKeyVals() As Variant) As Variant
' Description:Multiple Key (Left most) lookup
' Inputs: vTable Data table
' vResult Result Column (heading or #)
' vKeyVals Key Value(s) to search for
' Outputs: Me Success: Cell
' Failure: If UDF #REF Else Nothing
' Requisites: *None
' Example: ?XLookup("tblCities", "USA", "VA", "Norfolk", "Population")
' Date Ini Modification
' 12/07/17 CWH Initial Development
' 12/20/17 CWH Added special section for 1 key lookup
' Declarations
Const cRoutine As String = "XLookup"
Dim oLo As ListObject 'Table containing data
Dim vKeys As Variant 'vKeyVals 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 XLookup = 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(vKeyVals) = -1 Then Err.Raise DspError, , "#Key(s) required"
' When called by VBA, ParamArrays sometimes are stuffed in the first element
If IsArray(vKeyVals(LBound(vKeyVals))) Then _
vKeys = vKeyVals(LBound(vKeyVals)) Else _
vKeys = vKeyVals
' Procedure
With oLo
If Not .DataBodyRange Is Nothing Then
' Just 1 key - Use Worksheet.Function because it is fastest w/1 Key
If LBound(vKeys) = UBound(vKeys) Then
vKey = vKeys(UBound(vKeys))
If IsNumeric(vKey) Then vKey = CDbl(vKey)
lRow = Application.WorksheetFunction.Match( _
vKey, _
.ListColumns(1).DataBodyRange, _
0)
' More than 1 key - Use Worksheet.Evaluation because it is fastest w/multiple keys
Else
' Concatenate Key Values and Search Column Addresses
For lKey = LBound(vKeys) To UBound(vKeys)
lCol = lCol + 1
sCol = IIf(sCol <> vbNullString, sCol & " & ", vbNullString) & _
.ListColumns(lCol).DataBodyRange.Address
' Determine Key Value
If TypeName(vKeys(lKey)) = "Range" Then _
vKey = vKey & vKeys(lKey).Value2 Else _
If IsDate(vKeys(lKey)) Then _
vKey = vKey & CLng(vKeys(lKey)) Else _
vKey = vKey & vKeys(lKey)
Next
' Find Row # by Evaluating MATCH within the Table's worksheet
lRow = .Parent.Evaluate("=Match(""" & vKey & """," & sCol & ",0)")
End If
' Get Column #
lCol = .ListColumns(vResult).Index
' Return result
Set XLookup = .ListRows(lRow).Range(lCol)
End If
End With
ErrHandler:
If Err.Number > 0 Then
' Create sAddTxt (Additional Error Text) if needed
Select Case Err.Number
Case Is = 9: sAddTxt = "Column " & vResult & " not found in " & oLo.Name
Case Is = 13, 1004: sAddTxt = "Key(s) " & Join(vKeys, ",") & " not found"
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)
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: 'Pop Up Error Message
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 vKeyVals() 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 XLOOKUP(), vKeys(0) contains the array it needs.
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 XLOOKUP() uses cell parameters' VALUE2 for all range values.
Handling Errors:
If it were not for the possibility of errors this routine would just be a dozen lines. XLOOKUP() 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 XLOOKUP() from a spreadsheet formula or from VBA. We can determine which is the case by examining TypeName(Application.Caller). If it equals "Range", XLOOKUP() was called by a UDF as part of an Excel formula; otherwise, it was called by a VBA routine.
If we call XLOOKUP() from the spreadsheet we must return one of Excel's #ERROR values. In this case XLOOKUP() returns #REF if one of the parameters is wrong by setting XLookup = CVErr(xlErrRef).
If we call XLOOKUP() from a VBA routine XLOOKUP():
Returns results as objects or nothing so we can always SET our result variables.
Reports errors in message boxes.
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.
Speed:
This routine is designed primarily to simplify finding cells (the range object, not just the value) using multiple keys. Where performance is critical and VLOOKUP works (sorted list, single key in left most column, return value not range) 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