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.
How it works
XLOOKUP function is able to search sheets both vertically and horizontally, which wasn’t a possibility with VLOOKUP alone.
XLOOKUP(lookup_value,lookup_array,return_array)
- lookup_value: What you are looking for
- lookup_array: Where to find it
- return_array: What to return
Reducing the need for workarounds
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
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.
About The Author
Taylor Peterson is Third Door Media’s Deputy Editor, managing industry-leading coverage that informs and inspires marketers. Based in New York, Taylor brings marketing expertise grounded in creative production and agency advertising for global brands. Taylor’s editorial focus blends digital marketing and creative strategy with topics like campaign management, emerging formats, and display advertising.