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.
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