UDF: XLOOKUP – Using VLOOKUP for left AND right searches

This UDF creates a new formula called “XLOOKUP” – designed to replicate the VLOOKUP formula and provide the additional enhancement of looking in both directions.

However, this XLOOKUP formula looks left to right AND right to left. In order to get it to work, you have to specify a start column and result column.

E.g.

=VLOOKUP(F1,A:E,5,1)

This tells the UDF to search for the value in A1 in the fifth column of range A1, and return the value from the first column.

This is not possible using a normal VLOOKUP (as it only looks from left to right).

Option Explicit
 
Function XLOOKUP(SearchCriteria As String, SearchArea As Range, SearchColumn As Long, ResultColumn As Long)
 
 Application.Volatile
 Application.Calculation = xlCalculationManual
 
 Dim FirstColumn As Long
 Dim NumberOfColumns As Long
 Dim LastColumn As Long
 
 Dim FirstRow As Long
 Dim NumberOfRows As Long
 Dim LastRow As Long
 
 Dim i As Long
 Dim ASC As Long
 Dim ARC As Long
 Dim ResultRow As Long
 Dim RangeSheet As Worksheet
 Dim MatchFound As Boolean
 
 FirstColumn = SearchArea.Column 'THE FIRST COLUMN OF THE SEARCH RANGE
 NumberOfColumns = SearchArea.Columns.Count 'THE NUMBER OF COLUMNS IT SPANS ACROSS
 LastColumn = FirstColumn + NumberOfColumns - 1 'THE LAST COLUMN
 
 FirstRow = SearchArea.Row 'THE FIRST ROW OF THE SEARCH RANGE
 NumberOfRows = SearchArea.Rows.Count 'THE NUMBER OF ROWS IT SPANS ACROSS
 LastRow = FirstRow + NumberOfRows - 1 'THE LAST COLUMN
 
 'IF THE RANGE IS THE ENTIRE COLUMN, THEN REDUCE THE RANGE
 If NumberOfRows = Cells.Rows.Count Then _
 NumberOfRows = ThisWorkbook.Sheets(SearchArea.Parent.Name).UsedRange.Rows.Count
 
 'IF THE SEARCH COLUMN IS GREATER OR LESS THAN THE NUMBER OF COLUMNS IT SPANS ACROSS, THEN ERROR OUT
 If SearchColumn = 1 Then
 XLOOKUP = "#WrongSearchColumn"
 ElseIf SearchColumn > NumberOfColumns Then
 XLOOKUP = "#WrongSearchColumn"
 End If
 
 ASC = FirstColumn + SearchColumn - 1 'ACTUAL SEARCH COLUMN NUMBER
 ARC = FirstColumn + ResultColumn - 1
 
 Set RangeSheet = ThisWorkbook.Sheets(SearchArea.Parent.Name) 'SET THE SHEET YOU ARE WORKING ON AS THE CURRENT SHEET
 
 MatchFound = False 'SET THE MATCHFOUND = FALSE, TO PREVENT FALSE POSITIVES
 
 With RangeSheet
 
 'CHECK EVERY ROW OF THE SEARCH RANGE
 For i = FirstRow To LastRow
 
 'CHECK SPECIFICALLY IN THE SEARCH COLUMN
 If .Cells(i, ASC) = SearchCriteria Then
 
 'REMEMBER THE RESULT ROW
 ResultRow = i
 MatchFound = True
 Exit For
 
 End If
 
 Next
 
 'IF NO MATCH HAS BEEN FOUND, THEN
 If MatchFound = False Then
 XLOOKUP = "#NoMatchFound"
 Exit Function
 End If
 
 'IT WILL ONLY CONTINUE TO THIS POINT, IF MATCHFOUND = TRUE
 'USE THE RESULT ROW, AND THE ACTUAL RESULT COLUMN TO GET THE VALUE FROM THE OTHER COLUMN
 XLOOKUP = .Cells(ResultRow, ARC).Value
 
 End With
 
 Application.Calculation = xlCalculationAutomatic
 
End Function