Professor Excel
Convert XLOOKUP to VLOOKUP in Excel.

Convert XLOOKUP to VLOOKUP With These 4 Easy Methods!

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.

Prerequisites and disclaimer

Before we start, we unfortunately have to go through some requirements of your XLOOKUP formula as well as a disclaimer.

  1. These methods only work for a simple version of the XLOOKUP formula. That means that only the first three arguments are used. The last three arguments (which are optional) are not used).
  2. Also the formula is not wrapped in any other formula or contains other formulas within its arguments. If you have a formula combination, you’ll probably have to dig deeper – although methods 1 and 2 below might still help you.
  3. Please save backups of your work before you start. Especially the VBA macro (method 4 below) might cause irreversible changes to your formulas.

Method 1: Manually convert XLOOKUP to VLOOKUP

The first method – as usual – is the manual way. Often this is the fastest. Please follow these steps:

  1. Replace the X in XLOOKUP with V for VLOOKUP.
  2. The first argument stays the same.
  3. The second argument in the VLOOKUP formula is a comination of the 2nd and 3rd argument of XLOOKUP.
  4. Take a look at the 2nd and 3rd argument of XLOOKUP. How many column are there in-between. So if you are counting let’s say from column D to G, the “distance” of these two columns would be 3 columns (columns E, F and G). For VLOOKUP, you still have to add 1.
  5. The last argument is “FALSE”.
Steps to convert XLOOKUP to VLOOKUP
Steps to convert XLOOKUP to VLOOKUP

Example: Let’s take a look at an example. Your XLOOKUP formula looks like this: =XLOOKUP(A1,B1:B10,C1:C10)

Example of how to convert XLOOKUP to VLOOKUP in Excel.
Example of how to convert XLOOKUP to VLOOKUP in Excel.
  1. Replace XLOOKUP by VLOOKUP: =VLOOKUP(A1,B1:B10,C1:C10)
  2. The first argument (A1) remains the same: =VLOOKUP(A1,).
  3. Combine the first part of the second argument (B1 of the argument B1:10) and combine it with the last part of the 3rd argument: =VLOOKUP(A1,B1:C10,).
  4. Count the number of columns between the second and third argument of the original XLOOKUP formula and add 1: B to C has the distance of 1 column; after adding one, 2 is the third argument. =VLOOKUP(A1,B1:C10,2).
  5. Last argument is “FALSE”: =VLOOKUP(A1,B1:C10,2,FALSE).

Method 2: Transform cells containing XLOOKUP to hard values

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.

Copy & Paste

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.

VBA Macro

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):

  1. Open the VBA editor (Alt + F11 on the keyboard).
  2. Insert a new module.
  3. Paste the code below and place the cursor within the code.
  4. Press start.
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, professor, excel

Recommendation: Please check the example workbook. In the download workbook, please find the VBA macro in the module “modConvertValues”.

Method 3: Use an Excel formula to convert XLOOKUP to VLOOKUP or HLOOKUP

You don’t like VBA / macros? This methods works without any VBA code and with only minimal manual work.

Transform XLOOKUP to VLOOKUP with an Excel formula.

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.

  1. Copy the Excel formula (below) into an empty, unused cell. In the picture above, it was copied into cell C14.
  2. Replace all occurrences of “A1” by your XLOOKUP formula cell, B5. Fastest would be the Find & Replace function (press Ctrl + H on the keyboard to open the dialogue).
    The output of the formula is the converted VLOOKUP formula.
  3. Copy and paste it (as values) into cell B5. Press one time F2 and Enter in order to eventually convert the text to formula.
="=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, professor, excel

Recommendation: Check the example in the workbook. In the the download workbook, the Excel formula is located in cell C14 of the only worksheet.

Method 4: Convert all XLOOKUP formulas to VLOOKUP with this VBA macro

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:

  1. Please be careful, the XLOOKUP and therefore your workbook does not work as before.
  2. There is no undo function.
  3. Also this VBA macro only works for the simple version of XLOOKUP formula, having only 3 arguments and the lookup works from left to right (this article has more information about VLOOKUPs to the left).

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):

  1. Open the VBA editor (Alt + F11 on the keyboard).
  2. Insert a new module.
  3. Paste the code below and place the cursor within the code.
  4. Press start.
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, professor, excel

Recommendation: In the download workbook, please find the VBA macro in the module “modConvertVLOOKUP”.

Download

Download all examples above in this workbook.

Please feel free to download all examples, including all VBA macros in this article, from this link.




Excel opens blank window when double-clicking on file

Excel Opens Blank Window When Double-Clicking On File? Easy Solution!

You try to open an Excel file, but Excel doesn’t open it? Instead, a blank or empty Excel window is shown? This can be very annoying. Especially because it’s often not clear, what has happened. Google searches (to my experience so far) show many results, but nothing seems to be working. Or the steps in other articles are very complex and long. Often there is just a simple reason.

I receive a lot of Excel questions. This one is one of the rather annoying types… but luckily you can check out this easy solution!

The problem

When you double-click on an Excel file in the Windows Explorer Excel opens. But you only see a blank Excel window, similar to the picture below? Typically in this case, no error message pops up.

You open the an Excel file with a double-click and Excel shows a blank window?

Most common reason for blank Excel window when opening a file

Bevor you start with all these complex repair and “registry” options, please start with the most common solution. Your file might just be hidden within Microsoft Excel. And yes, that’s possible!

Please follow these two steps as shown in the screenshot below.

  1. Simply go to the View ribbon.
  2. If the “Unhide” button is not greyed-out, click on it.
  3. Excel now shows all the hidden Excel files that are actually open but not shown. So is your file among this list? Double-click on your file. Is it back now?

If your hidden Excel file is not on the “Unhide” list, you’ll probably have to dig deeper and find the real reason for why your Excel file is not showing up.

All other reasons

Other reasons could be the following:

  • Your Excel file is corrupt/broken.
  • Your Excel installation is broken. Try to use the Microsoft Office repair functionality or uninstall and re-install Office.
  • An Excel-add-in interferes with your file.

Usually, you’ll receive any kind of error message. Because for many other reasons for blank or empty Excel screens the internet has detailed but complex solutions, I’ll just recommend to Google for a solution.

Comments to Cells – 3 Methods for Comments and Notes (+Download)

Many Excel users like to use the comment function in Excel. Unfortunately, they come with some disadvantages (displaced, wrong size, hiding content, bad for printing, often outdated, e.g.) so that I usually recommend not to use them. Instead, I recommend using a comment column. This article describes how to transform Excel comments to cells. Extract comment text or note text of an Excel cell with these 3 methods.

[…]

Return Number Format Codes in Excel

Return Number Format Codes in Excel – 4 Ways to Get the Formatting Code from a Cell

Excel is a great software. It’s easy to use (at least the basic functions…) and very flexible. Unfortunately, coming with the flexibility, users tend to misuse the options and disobey certain basic rules. One thing I’ve seen multiple times is to transport important information in the formatting of a cell. It might be the background color, font color or strike-through. In this article, we’ll talk about something related: Return number format codes.

Problem: Information is stored in the number format code of the Excel cell, for example the currency "USD" or "EUR".

Problem: Information is stored in the number format code of the Excel cell, for example the currency “USD” or “EUR”.

Let’s assume the following example. You receive a table containing prices with two columns. The first column contains the name of the item, for example “Product A”. The second column contains the respective prices. The problem is that instead of having one common currency, the currency information is only given in the number format code. Learn four methods in this article of how to return number format codes in Excel.

Please note: If you want to know how to use custom number format codes, please refer to this article. On this page we only explore how to return number format codes.

[…]

Merge Excel files in Excel: 6 Methods

Merge Excel Files: How to Combine Workbooks into One File

You have several Excel workbooks and you want to merge them into one file? This could be a troublesome and long process. But there are 6 different methods of how to merge existing workbooks and worksheets into one file. Depending on the size and number of workbooks, at least one of these methods should be helpful for you. Let’s take a look at them.

[…]

Disable GETPIVOTDATA permanently in Excel.

GETPIVOTDATA: Disable GETPIVOTDATA permanently in Excel

Does this sound familiar to you? You want to refer to a cell within a PivotTable, let’s say cell C6. But instead of getting =B6, Excel does something like =GETPIVOTDATA(“Value”;$A$3;”Name”;”c”). This article shows you how to permanently disable GETPIVOTDATA in Excel.

[…]

FIELDVALUE formula in Excel

FIELDVALUE Formula in Excel: Insert Data of Companies and Countries (+Download)

Excel has – in it’s newest version – a quite useful new formula type. It’s called “linked data” and offers the functionality to automatically insert data from the internet to your table. This can be done with the FIELDVALUE formula and works in a first test quite well. Unfortunately, the available data types and options are limited so far. But let’s see how it works first.

[…]

How to Prevent Add-Ins to Disappear in Excel.

Excel Add-In Disappeared? 4 Methods to Prevent an Add-In to Disappear

You are using an Excel add-in that keeps disappearing? Also our add-ins, for example Professor Excel Tools, sometimes might disappear after restarting Excel. This article introduces four methods of how to prevent Excel (and other Microsoft Office) add-ins from disappearing.

[…]

Sort Worksheets in your Excel Workbook: 3 Simple Methods.

Sort Excel Sheets: 3 Simple Methods (+Download)

Especially for large Excel workbooks with many sheets it’s crucial to organize the content. You should—for example—sort worksheets in a logic sequence. One way to sort sheets would be in an alphabetical order. This article introduces three simple methods for sorting Excel worksheets.

[…]