If You Use Excel’s Vlookup(), You Need to Be Using the New’Ish Xlookup() Now

Do you use the Excel function Xlookup() yet? If you use Microsoft Excel as much as I do, you probably look stuff up using the tried and true Vlookup() or it’s less popular sibling, Hlookup() function. It is awesome, until it is not and then its very frustrating. Well, in 2020 Microsoft introduced a new and improved way to look data up in tables called XLookup(). XLookup() is so much better that I thought it might be worth a quick post to introduce it to users who may not be aware of it. I missed it for a good three years before I stumbled across it last year.

But Wait, This Blog is About Engineering not Excel Functions like XLookup()?

Let’s be honest, as much as I love and have built a career around engineering software like Ansys Mechanical and SolidEdge (yes, I still use SE and have refused to learn SW well… I’m stubborn) or C programming, the most powerful and most used engineering software tool out their is Microsoft Excel. And, as PADT has grown and I’ve spent more time dealing with business type data, my usage of Excel has grown even more. And a lot of what we do for Excel for engineering or business is create tables of data, then look stuff up on tables.

For some pointers on what Ansys users should know about Microsoft Excel, check out this post from 2012: “Some Stuff ANSYS Users Should Know about Excel.” It still holds up

A Review of VLookup()

Vlookup() allows users to specify a table of data, specify a value to find in the first column of that table, then return a value from another column on the same row it finds the search value:

VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup])

The problem I have with Vlookup is that it uses the first column, always, as the key to look stuff up in. So If if I wanted to know who the manager of the (fake) Sacramento office is, I would have to specify the two columns in my table. If I wanted to know who the employee is, I’d have to rearrange the table so the City is first or use Index(), which I never like:

This will update if I move the table, but if I just gave a range then things break. Also, if I want to work on data that is horizontally arranged, I have to use HLookup()

The thing I HATE most about VLookup() is the column index. You have to count columns and if you add or delete a column, it all breaks. Bah.

Finally, We have Xlookup()

If you go to the online help for Vlookup(), it even tells you to go use Xlookup(). As frustrating as VLookup() can be, I have used it for 30+ years now, hundreds of times. So I do feel a little sad for this aging function.

Here is the syntax for Xlookup():

=XLOOKUP(lookup_value, lookup_array, return_array, 
          [if_not_found], [match_mode], [search_mode])
- lookup_value: The text or number you are looking up
- lookup_array: The 1D range to search for lookup_value
- return_array: The 1D Range to return the value from
- if_not_found: A string to return if you can't what you are looking for.
- match_mode: 0 - Exact match. Return #N/A if no match. Default
             -1 - Exact match. If not found return the next smaller item
              1 - Exact match. If not found, return the next largest item
              2 - Wildcard Match. It takes *, ? and ~
- [search_mode]: Specifies how to look for a match:
              1 - Perform a search starting at the first item. Default
             -1 - Perform a reverse search starting at the last item.
              2 - Perform a binary search (lookup array must be sorted ascending)
             -2 - Perform a binary search (lookup array must be sorted descending)

Look! No column index! It works of the idea that you have a 1D range. It knows coumns vs rows, but you can’t mix a row array and a column array. Also of note, if you have a big table of data, use the search_mode 2/-2 options to speed things up with a binary search.

Most of the time, you will just specify the lookup_value and lookup_array. One thing to note is that this function works on arrays, so it can return multiple columns of data.

Here is a basic example with two tables: EmpList and Emp_Type. What I want to do is get information by

Using Sacramento as the location, I can concatenate and get the name, I can specify a range to get the last then first name, I can grab the manager name, or I can use the response from one XLookup() as the input for a second Xlookup( ) in a separate table to translate an acronym.

You can also work with vertical values. In this example I’m looking up the index and RGB values of colors:

I almost never work with ranges, I always use tables, but I tossed this in here as an example. Still, so much easier to specify what you want and get what you want.

Side note, if you want the VBA that sets the colors next to the RGB values, it is included as a text block in the sample sheet. I had Co-Pilot generate the script for me.

The third example I have is something I’ll use with Engineering a bit more, interpolation. Now this function doesn’t interpolate, but it does return value you can use Trend() with to interpolate:

Just a Taste of XLookup()

There are far better tutorials and even videos on all of this. My goal here was to just let everyone know the function is out there and show some short examples of how to use it.

Here is the Excel workbook I used (without the macro and in a zip file):

Hopfully this will make your Excel adventures even more enjoyable and productive.