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.
Leave a Reply
You must be logged in to post a comment.