How To Use Excel's Xlookup, The Newer And More Powerful Vlookup

I still remember the first day I figured out how to run a vlookup command in Excel; I was sitting at my coffee table wondering what the point of this silly little command was. Once I realised its power, I never went back. As a business analyst at Stanford University, I turned to vlookup constantly to get through my day’s work.

What’s a vlookup, you ask? For the uninformed, this fun little Excel command allows you to extract a value based on the position of another value. So, for example, suppose you had a spreadsheet that looked like this:

If you run a vlookup, then you can tell the cell (f5) to look for a value in a column (let’s say, “bus”), and then return a value from another column in the same row (green). Hlookup functions the same way, just reverse the row/column designations: you’re looking up a value in a row and then returning a value from another row further down.

And now we arrive at xlookup, the “successor to the iconic vlookup function,” as Microsoft’s Joe McDaid describes. Here’s how it works:

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

So, back to my example, if you wanted to find what colour is associated with the word “house” and extract that information into a cell, you’d simply have to type in:

xlookup(“house”,a:a,d:d)

Easy.

Xlookup, in addition to being a shorter command to have to mess with than vlookup, also gives you access to a feature astute vlookup’ers have dreamed about for years: reverse searches. In other words, you can now also pull in value that are left of an anchor point, rather than the default right. It’s practically magic.

Unfortunately, not everyone is going to get to play with xlookup today. Microsoft is rolling out the function to Office 365 Insiders first. So, you better get in the beta if you want to master xlookup before everyone else.


Comments

    So, back to my example, if you wanted to find what colour is associated with the word “house” and extract that information into a cell, you’d simply have to type in:

    xlookup(“house”,a:a,d:d)

    It would be xlookup("house",a:a,c:c), since column C has the colours in it.

      Also, in the first example given, the colour "green" is not in the same row as "bus". So, the Author didn't do much checking...

    Looks interesting, I wonder how it compares to the typical Index-Match (which I use if I need to go left). I wonder if the return_array needs to be in the same line as the lookup array, or if it also gives an index, as it will let me offset as well.

    Index Match anyone?
    Faster (performance wise) than Vlookup, and doesn't care about column order. I wonder what the performance of Xlookup will be like.

Join the discussion!

Trending Stories Right Now