fbpx

Microsoft Excel’s Xlookup Is a New and Improved Vlookup, to the Delight of Data Junkies Everywhere

Photo Credit: Needpix

Vlookup is an Excel command allowing you to extract a value based on the position of another value. So, you can run a vlookup to tell a cell to look for a value in one column and retrieve another value from the same row.

Photo Credit: Needpix

Okay, forget all that because now we have Xlookup, which Microsoft’s Joe McDaid called “successor to the iconic vlookup function.” He also says,

XLOOKUP is named for its ability to look both vertically and horizontally (yes it replaces HLOOKUP too!). In its simplest form, XLOOKUP needs just 3 arguments to perform the most common exact lookup (one fewer than VLOOKUP). Let’s consider its signature in the simplest form:

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

As an example, McDaid built a simple spreadsheet with one column of countries and another column of their international calling codes. He created the following command to quickly find the international calling code for Brazil:

XLOOKUP(the value of what he was looking for [which was Brazil in F3], the value of where to look [the range of the entire country column which was B3:B12], the value of what to retrieve [the range of the entire international calling code column which was D3:D12]).

It looked like this: XLOOKUP(F3,B3:B12,D3:D12)

Instantly, the value of Brazil calling code populated the formula’s cell.

Photo Credit: Pxhere

The Xlookup is shorter, faster and easier to use than the old-tymie Vlookup. And, BONUS, you can do reverse searches so you can retrieve data from the left of the anchor point instead of only defaulting to the right. OMG.

The rollout started with 365 Office Insiders getting first dibbies, with gradually larger availability. This is a really cool update for spreadsheets so make sure you update your spreadsheet of updates with this one.

Enjoy!