For the folks who are working in ad operations and PPC, the VLOOKUP function (and even its counterpart, HLOOKUP) has been a staple of data manipulation in Excel. After over almost 3 and a half decade, VLOOKUP is making way for a successor.
Microsoft announced the rollout of XLOOKUP a new function that is designed to address many of the known limitations of VLOOKUP. For marketers and advertisers, this means a more effective reporting with less time spent working on workaround functions.
VLOOKUP was only able to search sheets vertically, and HLOOKUP horizontally, but XLOOKUP can search both vertically and horizontally. XLOOKUP reqires just three inputs in order to perform the most common exact lookup:
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
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.