Xlookup Searching All Entries Of A Lookup Table
This example shows how to use xlookup to search for an exact match in a translation table that is not sorted.
Program Code
Option Explicit
Public Sub XLOOKUP_Example()
' ****************************************************************************************
' Get Sales Rep using Customer name
' ****************************************************************************************
Dim wkbCustomerTemplate As Workbook
Dim wksShippingLog As Worksheet 'Lookup Data Source
Dim wksAccountPerformance As Worksheet 'Lookup Data Target- Put Return Value in Column 1
Dim i As Long
Dim lngLastRowShippingLog As Long 'Lookup Source
Dim lngLastRowAccountPerf As Long 'Lookup Target
Dim rngLookupTableLocate As Range 'Full Set Of Items To Find
Dim rngLookupTableReturn As Range 'Full Set Of Answers To Return
Application.ScreenUpdating = False
' ****************************************************************************
' Set Up Workbook and Worksheet Variables for the Production Sales Detail
' Workbook Opened by the User
' ****************************************************************************
Set wkbCustomerTemplate = ActiveWorkbook
Set wksShippingLog = wkbCustomerTemplate.Sheets("ShippingLog") 'Source
Set wksAccountPerformance = wkbCustomerTemplate.Sheets("Account Perf") 'Target
' ****************************************************************************
' Clear Empty Rows
' ****************************************************************************
Do Until wksAccountPerformance.Cells(1, 1) <> ""
wksAccountPerformance.Rows("1:1").Delete Shift:=xlUp
Loop
' ****************************************************************************
' Compute the Number Of Rows Of The Lookup Source and Target
' ****************************************************************************
lngLastRowShippingLog = wksShippingLog.Cells(wksShippingLog.Rows.Count, "A").End(xlUp).Row
lngLastRowAccountPerf = wksAccountPerformance.Cells(wksAccountPerformance.Rows.Count, "B").End(xlUp).Row
' ****************************************************************************
' Set Range For Full Set Of Lookup Table Values To Match To The Search Arg
' ****************************************************************************
Set rngLookupTableLocate = wksShippingLog.Range(wksShippingLog.Cells(2, 1), wksShippingLog.Cells(lngLastRowShippingLog, 1))
' ****************************************************************************
' Set Range For Full Set Of Lookup Values To Return As The Search Answer
' ****************************************************************************
Set rngLookupTableReturn = wksShippingLog.Range(wksShippingLog.Cells(2, 8), wksShippingLog.Cells(lngLastRowShippingLog, 8))
For i = 1 To lngLastRowAccountPerf
' ****************************************************************************
' Eliminate Leading Empty Rows
' ****************************************************************************
If Len(wksAccountPerformance.Cells(i, "B")) < 5 Then
GoTo GetNextAccountPerf
End If
' ****************************************************************************
' Perform The Search
' ****************************************************************************
wksAccountPerformance.Cells(i, 1) = WorksheetFunction.XLookup(wksAccountPerformance.Cells(i, 2), _
rngLookupTableLocate, _
rngLookupTableReturn, _
"XXX", 0, 1)
GetNextAccountPerf:
Next i
MsgBox ("Process Is Complete - Save the Updated Sales Report" & vbCrLf & _
"To Your Target Directory")
End Sub