XLOOKUP

Inspired by my favorite Workout Videos (X is for "Xtreme")

Call up your ex, drink some X-lax, and watch some X-Games! It's time for some X-Treme X-cel funXtions! Yeah. I'm so pumped.

In all seriousness, though, I learned this function about a week after publishing the video on VLOOKUPs. I almost feel bad for not knowing more about the function by the time I learned it. It came out in 2019! I mean, in order for it to take me this long to learn, the world would have just about had to come to an end around that time...oh.

So in the old world (the VLOOKUP world), if your return data was to the left of your lookup data, then you had two choices.

  1. Rearrange the data columns, or

  2. Use an index/match function, which is a fun little combination of stress and confusion for the average user.

I am not going into those options, as they are completely useless with the advent of the XLOOKUP. Now, you can look in either direction (plus, this also works the same way in replacing the HLOOKUP, if you use that...which you probably don't).

Advantages of both types of functions

So How Does it Work!?

So, as shown below, there are 3 primary criteria needed to execute this function:

  1. The Lookup value

    1. D2 in the example to the right

    2. This is the key you're using to find the data you want to return

  2. The Lookup array

    1. Column D (D:D) to the right

    2. The range of cells you expect to find your lookup value above in)

3. The Return Array

    1. Column B (B:B) to the right

    2. The range of cells adjacent to your lookup array that you want to pull (or return).

The "0" at the end is for error handling, and we'll handle that later. Don't get ahead of yourself, rookie.

So, in the end, the Formula Looks like this:

=XLOOKUP(Lookup Value, Lookup Array, Return Array, Error handle)

Now kids, it's time for an example.

Example - Combining Reports

So let's say you have customer payment data that doesn't match your invoices. This is a common practice in the automotive industry, where payment is based on the shipment--not a copy of the invoice. But how is the pricing kept up with? Well that's easy--both companies agree to a price ahead of time, then one forgets to update a few parts correctly, and pricing becomes a proverbial soup-sandwich. But that's beside the point. You've got some invoices to match to payment.

Customer Payment Data
(XLOOKUP will be in column G)

Invoice Data you'll need to Identify

So, assuming we live in a magical world where you only need to pull the invoice, we need to identify our lookup value and arrays. In order do so, we need to find the columns with unique values in every row. If you look closely, you'll notice this is the "Shipment Number" columns in both worksheets. So, we've identified our Lookup value (Cell B2 in the Payment tab, and column B in the Invoice History tab). Why? Because you're Lookup value is is always going to be in the table/worksheet you're pulling data into. What you're basically asking Excel to do is, "give me the corresponding value that lines up with this value in table B and bring it into table A." Lastly, since you're trying to bring an invoice number into the Payment tab, you want to set your invoice number column as your Return Array. This is what we'll type into cell G2 of the Payment tab:

=XLOOKUP(B2,'Invoice History'!B:B,'Invoice History'!A:A,"")

Dynamic Ranges

Say you wanted to drag this formula over, and pull All Columns from the Invoice History tab. All you have to do is add some absolute references to the Lookup value and array, and you can drag the formula over and down. Absolute references are just the cell or column with dollar signs in them. This makes your function dynamic--which mean it can move around without being corrupted...like Texans. Your new version of the formula would be:

=XLOOKUP(B$2,'Invoice History'!$B:$B,'Invoice History'!A:A,"")


If you notice the error handle at the end (The "" at the end of the function), this returns a blank cell. Anytime you see quotation marks in Excel, this basically references a text string. So instead of "", you could input "Not found", or "Oooof" or anything that tells you that you'll have to go do some digging on your side. But this function won't help you with that.

If you made it this far, thanks for reading. If all you did was scroll to the function setup, then you're a kindred spirit, and I respect that. Have a great day everyone, and Shine on.