Tech Tip – XLOOKUP vs VLOOKUP

 I  spend a lot of time comparing spreadsheets. Invariably, I have one sheet of donor information, and a second sheet with a key piece of information missing from the first, like an email address. With tasks like this, VLOOKUP was my go-to solution. Fun fact: for years, VLOOKUP was the third most used function in Excel, behind only SUM and AVERAGE. But, there’s a new formula is town: XLOOKUP, and it’s a powerful alternative to the older, clunky VLOOKUP.  

 Here’s how they compare: 

• VLOOKUP only works left-to-right and requires column numbers, which can be tedious with large sheets or layout changes. 

• XLOOKUP searches in any direction, removes the need for column numbers, and handles missing values better. 

Using donor spreadsheets as an example: 

• Sheet1: Constituent ID is in column C. 

• Sheet2: Data is in A2:F30, with Constituent ID in column F and Email in column B. 

To bring the email from Sheet2 into Sheet1: 

With VLOOKUP: =VLOOKUP(C2, Sheet2!A2:F30, 2, False). This fails because VLOOKUP can only search left-to-right, and if the “True” default isn’t changed to “False,” you risk incorrect results. 

With XLOOKUP: =XLOOKUP(C2, Sheet2!F:F, Sheet2!B:B). It works effortlessly, searching column F for the ID and pulling the email from column B. You can even add custom messages for missing data, though we used default settings here. 

Though XLOOKUP is more intuitive, it’s not available in older Excel versions, so keep both formulas handy! 

For more details, check out this tutorial from Excel Dictionary, one of my favorite resources.