## 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
```