How To Use A Double XLOOKUP Formula
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.
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.
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.
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).
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.
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.
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.
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!
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!
Learn my best Excel chart design hacks. These will turn your ugly charts into professionally crafted graphics, leaving your boss speechless!!!
Keep Learning
![How To Use Excel’s XLOOKUP Function [Guide 2022]](https://www.thespreadsheetguru.com/wp-content/uploads/2022/12/Microsoft-Excel-XLOOKUP-Function-Guide.png)
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.