×

How To Use A Double XLOOKUP Formula

By Chris Newman •  Updated: 09/20/19 •  4 min read
Setting Up A Double Xlookup Formula in Excel

Have you heard of an XLOOKUP/XLOOKUP?

No, I didn’t stutter in my writing….there is now such a thing as a Double XLOOKUP formula and it acts as an alternative to the ever-popular Index/Match/Match formula.

Below is an example of how a Double XLOOKUP works to allow for dynamic vertical AND horizontal lookup inputs.

Double Xlookup Example in Microsoft Excel

Yes, I know….it looks a little weird but let me dig in a bit and show you how this formula works.

Step 1: Lookup ID

First, we will work on the first XLOOKUP function which requires the location of the lookup value (in this case the Employee ID is going to be our unique identifier). Make sure to include your dollar sign ($) in front of the column reference so when we drag this formula across to pull in the City data, the Employee ID is still referenced.

xlookup function - lookup value input

Step 2: ID Column

Next, we will want to reference the column with all the Employee IDs in our second table. This will allow us to essentially “link” the two tables together and return data into our destination table. Again, make sure you make this column reference absolute with the dollar signs ($), as we what this part of the formula to remain static as the formula gets copy/pasted around the spreadsheet.

xlookup function - lookup array input

Step 3: Dynamic Return Column

OK, everything has been pretty straightforward so far, right?! Now it’s time for the tricky part.

Our third input to complete our initial XLOOKUP requires us to reference the column holding the data in which we would like to return (in this case, the Name column).

xlookup function - return array input

But notice if we drag the formula over, the wrong value is returned for City. In order to get the correct data to be looked up, we need to make an adjustment to the formula to point specifically to column K.

xlookup function - return array input

So how can we write an XLOOKUP formula where the return column is dynamic? If we want this column reference to be “smart”, we are going to need to utilize another XLOOKUP!

Did you know XLOOKUP returns a range?

Yes, that’s right! XLOOKUP does not produce a single value, but results in a cell range reference. For example, if Cell A1 = 6 and XLOOKUP does its lookup thing and lands on cell A1, it actually returns A1 instead of 6. This very fact will allow us to create a dynamic range reference.

For this second XLOOKUP we are going to begin by mimicking an HLOOKUP in order to determine which column we need to return. So we will reference our header cell (in this case “Name” in Cell C2) and look for that value in the header range of our second table.

Double xlookup formula Excel spreadsheet

Now here is the key to successfully writing a valid Double XLOOKUP formula! Instead of finding a single cell intersection with this XLOOKUP, we want to find all the cells rows associated with the lookup. To do this, we will reference the entire data table, instead of a single row.

FORMULA: = XLOOKUP( C$2, $I$2:$K$2 , $I$2:$K$10 )

You’ll notice when this is carried out in the example below, the result actually comes back as a dynamic array because the intersection included the entire column. While it’s nice to see all the names show up, the key is the formula is really giving us a result of I2:I10, which is exactly the reference we need for our first XLOOKUP function.

xlookup returns dynamic array result

This entire XLOOKUP formula will not become our formula to return the range reference required for the third input of our initial XLOOKUP function.

NOTE: Make sure you pay attention to your dollar signs in the second XLOOKUP. The second and third inputs will always need dollar signs for both the column and row references.

Putting it all together

If we take all three parts and smash them all together, we get a Double Lookup formula!

double xlookup example Microsoft excel

Have I Converted You?

While the Double XLOOKUP is a little tricky at first, I think it is much more streamlined and quicker to throw together than the INDEX/MATCH/MATCH formulas we’ve been using to accomplish the same functionality. What do you think? When you finally get your hands on XLOOKUP are you going to switch or are you an “Index-Matcher” for life? Let me know your thoughts in the comments below!

Keep Learning

Chris Newman

Chris Newman

Chris is a finance professional and Excel MVP recognized by Microsoft since 2016. With his expertise, he founded TheSpreadsheetGuru blog to help fellow Excel users, where he shares his vast creative solutions & expertise. In addition, he has developed over 7 widely-used Excel Add-ins that have been embraced by individuals and companies worldwide.