

The new XLOOKUP formula in Excel is great. It’s very powerful and solves many problems the iconic VLOOKUP (or HLOOKUP) formula cannot do. But there is one problem: It’s only available in the newest Excel versions. Older versions – which are still around a lot – can’t use them. In this article we explore 4 ways to convert XLOOKUP to VLOOKUP.
Contents
Before we start, we unfortunately have to go through some requirements of your XLOOKUP formula as well as a disclaimer.
The first method – as usual – is the manual way. Often this is the fastest. Please follow these steps:
Example: Let’s take a look at an example. Your XLOOKUP formula looks like this: =XLOOKUP(A1,B1:B10,C1:C10)
The second method – transforming formula cells to “hard” values – can be achieved in two ways. Manually by copying and pasting respective cells or “automatically” by using a slightly smarter VBA macro.
Select the cell are range of cells containing XLOOKUP formulas. You can use the search function to identify these cells. As a side note: Please don’t copy and paste non-coherent cells.
Now copy these cells by pressing Ctrl + C on the keyboard. Next paste these cells with “Paste special”: Hold Ctrl + Alt on the keyboard and press V. Select “Values” and click on “OK”.
Please refer to this article for more information about pasting formulas as values only.
You don’t like to convert XLOOKUP formula cells to values manually but rather use a VBA macro? Please feel free to use the following macro. It converts all cells that have formulas and have the string XLOOKUP within them to values. Of course the warning: Please be careful, the XLOOKUP and therefore your workbook does not work as before. And there is no undo function.
Add the code to your Excel file (please refer to this article for more detailled description of how to add VBA code to your Excel file):
Sub Convert_XLOOKUP_to_Values()
Dim formula_text As String
For Each cell In ActiveSheet.UsedRange
If cell.HasFormula = True Then
formula_text = cell.Formula
If InStr(1, formula_text, "XLOOKUP") > 0 Then
cell.Select
cell.Copy
cell.PasteSpecial xlPasteValues
End If
End If
Next
Application.CutCopyMode = False
End Sub
Recommendation: Please check the example workbook. In the download workbook, please find the VBA macro in the module “modConvertValues”.
You don’t like VBA / macros? This methods works without any VBA code and with only minimal manual work.
The idea: You copy and paste a long formula (see below) into your sheet. Then you just do a tiny modification (by search & replace). The output of the formula is a VLOOKUP formula, based on your input XLOOKUP formula.
So let’s assume that your XLOOKUP formula is located in cells B3.
="=VLOOKUP("&MID(FORMULATEXT(A1),FIND("(",FORMULATEXT(A1))+1,FIND(",",FORMULATEXT(A1))-FIND("(",FORMULATEXT(A1))-1)&","&MID(FORMULATEXT(A1),FIND(",",FORMULATEXT(A1))+1,FIND(":",FORMULATEXT(A1))-FIND(",",FORMULATEXT(A1))-1)&":"&MID(FORMULATEXT(A1),MATCH(2,1/(MID(FORMULATEXT(A1),SEQUENCE(LEN(FORMULATEXT(A1))),1)=":"))+1,LEN(FORMULATEXT(A1))-MATCH(2,1/(MID(FORMULATEXT(A1),SEQUENCE(LEN(FORMULATEXT(A1))),1)=":"))-1)&","&COLUMN(INDIRECT(MID(FORMULATEXT(A1),MATCH(2,1/(MID(FORMULATEXT(A1),SEQUENCE(LEN(FORMULATEXT(A1))),1)=":"))+1,LEN(FORMULATEXT(A1))-MATCH(2,1/(MID(FORMULATEXT(A1),SEQUENCE(LEN(FORMULATEXT(A1))),1)=":"))-1)&"1"))-COLUMN(INDIRECT(MID(FORMULATEXT(A1),FIND(",",FORMULATEXT(A1))+1,FIND(":",FORMULATEXT(A1))-FIND(",",FORMULATEXT(A1))-1)&"1"))+1&",FALSE)"
Recommendation: Check the example in the workbook. In the the download workbook, the Excel formula is located in cell C14 of the only worksheet.
The last method is also the most complex one: Convert all XLOOKUP formulas in your current worksheet to VLOOKUP with a VBA macro.
One more time the warning:
Add the code to your Excel file (please refer to this article for more detailled description of how to add VBA code to your Excel file):
Sub Convert_XLOOKUP_to_VLOOKUP()
'
' Convert_XLOOKUP_to_VLOOKUP Macro
' Converts all XLOOKUP formulas to VLOOKUP. Careful: Only works with simple XLOOKUP formulas. Please save a backup first - undo not possible.
Dim proceed As Boolean
proceed = MsgBox("Converts all XLOOKUP formulas to VLOOKUP. Careful: Only works with simple XLOOKUP formulas. Please save a backup first - undo not possible.", vbOKCancel, "Careful!")
If proceed = True Then
Dim formula_text As String
Dim xlookup_arguments() As String
Dim vlookup_arguments(2) As String
Dim first_cell As String
Dim second_cell As String
Dim complete_formula As String
Dim n As Integer
n = 0
'Go trough all cells in used range
For Each cell In ActiveSheet.UsedRange.Cells
'Only regard formula cells
If cell.HasFormula = True Then
formula_text = cell.Formula
'Only use formulas starting with XLOOKUP
If Left(formula_text, 9) = "=XLOOKUP(" Or Left(formula_text, 1) = "=+XLOOKUP(" Then
'Split the formula into its arguments
xlookup_arguments = splitString(CStr(formula_text))
'Write new arguments one by one
vlookup_arguments(0) = xlookup_arguments(0)
first_cell = Split(xlookup_arguments(1), ":")(0)
second_cell = Split(xlookup_arguments(2), ":")(1)
vlookup_arguments(1) = first_cell & ":" & second_cell
'Handle difference of column cell range ("A:B") and other cell range ("A1:B5")
If string_has_number(CStr(first_cell)) = True Then
number_of_columns = Range(second_cell).Column - Range(first_cell).Column
Else
number_of_columns = Range(second_cell & "1").Column - Range(first_cell & "1").Column
End If
'add one for VLOOKUP distance because VLOOKUP starts counting with the first column/cells
vlookup_arguments(2) = number_of_columns + 1
complete_formula = "=VLOOKUP(" & vlookup_arguments(0) & "," & vlookup_arguments(1) & "," & vlookup_arguments(2) & ",FALSE)"
cell.Formula = complete_formula
n = n + 1
End If
End If
Next
Dim confirm As Boolean
If n = 0 Then
confirm = MsgBox("No XLOOKUP formula converted", vbExclamation, "Done")
Else
confirm = MsgBox(n & " XLOOKUP formulas to VLOOKUP converted. Please check them and if necessary revert back to your previously saved backup file.", vbExclamation, "Done")
End If
End If
End Sub
Function splitString(formula_text As String)
Dim Result() As String
Dim DisplayText As String
Dim arguments As String
arguments = Replace(formula_text, "=XLOOKUP(", "")
arguments = Replace(arguments, "=+XLOOKUP(", "")
Result = Split(arguments, ",")
Result(2) = Replace(Result(2), ")", "")
splitString = Result
End Function
Function string_has_number(first_cell As String) As Boolean
Dim i As Integer
For i = 1 To Len(first_cell)
If IsNumeric(Mid(first_cell, i, 1)) Then
string_has_number = True
Exit Function
End If
Next
End Function
Recommendation: In the download workbook, please find the VBA macro in the module “modConvertVLOOKUP”.
Please feel free to download all examples, including all VBA macros in this article, from this link.
'Professor Excel Tools': Add more than 60 amazing features to Excel!
Professor's Error Helper for Excel: Solve any error in Excel.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.
Subscribe to our popular Excel newsletter! You get all this:
Your welcome gift: Our big 45 pages keyboard shortcuts package.
Subscribe now! In case the sign-up form above doesn't work, please use this page. Sorry for the inconvenience.
Please use this download link to download the Currency Converter Excel add-in!
Please use this download link to download the comment extraction example: http://bit.ly/Extract_Comment_Example