Microsoft introduces XLOOKUP in Excel – and it’s a big deal for data reporting

For those working in ad operations and PPC, the VLOOKUP function (and HLOOKUP – its horizontal counterpart) has been a tried-and-true staple of data manipulation in Excel. Now, after more than 34 years as a cornerstone lookup function, VLOOKUP is making way for a new successor.

Microsoft has announced the rollout of XLOOKUP – a powerful new function designed to address many of the known limitations of VLOOKUP. For advertisers and marketers, this means more efficient reporting with less time spent performing workaround functions. It is rolling out for Office 365 Insiders Program users over the next few weeks.

How it works

XLOOKUP function is able to search sheets both vertically and horizontally, which wasn’t a possibility with VLOOKUP alone. XLOOKUP requires only three inputs in order to perform the most common exact lookup:

XLOOKUP(lookup_value,lookup_array,return_array)

1. lookup_value: What you are looking for

2. lookup_array: Where to find it

3. return_array: What to return

XLOOKUP function in action.

Reducing the need for workarounds

XLOOKUP takes aim at the following VLOOKUP limitations outlined by Microsoft:

Defaults to an “approximate” match: Most often users want an exact match, but this is not VLOOKUP’s default behavior. To perform an exact match, you need to set the 4th argument to FALSE. If you forget (which is easy to do), you’ll probably get the wrong answer.

Does not support column insertions/deletions: VLOOKUP’s 3rd argument is the column number you’d like returned. Because this is a number, if you insert or delete a column you need to increment or decrement the column number inside the VLOOKUP.

Cannot look to the left: VLOOKUP always searches the 1st column, then returns a column to the right. There is no way to return values from a column to the left, forcing users to rearrange their data.

Cannot search from the back: If you want to find the last occurrence, you need to reverse the order of your data.

Cannot search for next larger item: When performing an “approximate” match, only the next smaller item can be returned and only if correctly sorted.

References more cells than necessary: VLOOKUP 2nd argument, table_array, needs to stretch from the lookup column to the results column. As a result, it typically references more cells than it truly depends on. This could result in unnecessary calculations, reducing the performance of your spreadsheets.

Why we should care

For advertisers and marketers who rely on Excel for day-to-day reporting, the new XLOOKUP function will reduce the time it takes to match and analyze data from varying sources, such as from ad platforms, servers and CRMs.

XLOOKUP will be able to replace VLOOKUP, HLOOKUP, and INDEX/MATCH by enabling the selection of two columns (instead of the whole range) and allowing columns to be inserted into the desired data range without needing to change the column numbers.