How to use XLOOKUP with two sheets?

Learn how to use Excel XLOOKUP function with two sheets in this step-by-step tutorial.

Why you may want to use XLOOKUP with two sheets?

If you have data in two places (sheets or Excel workbooks) like depicted above, you may want to use XLOOKUP to combine data from both places to get the full picture. For example,

• Student list in sheet1, course price list in sheet2, you want to know the price of courses against student names in sheet1
• Invoice list in sheet1, payment list in sheet 2, you need to know which invoices are paid up (reconciliation)
• Equipment list in sheet1, inspection details in sheet2, you want to know when the last inspection date is for each equipment

What you need?

• You need two sheets of data.
• or if data is in two separate Excel files, then open both files.
• If you need a sample data file, grab my free xlookup two sheets template.

XLOOKUP with two sheets (step-by-step instructions)

Step 1: Identify common column between both sheets

For example, in the above scenario, “Fee” is the common column between sheet 1 (student list) and sheet 2 (course list).

Step 2: Write the XLOOKUP formula

Go to the sheet where you want to get the data from “other” sheet and write the XLOOKUP function using the pattern below.

=XLOOKUP(
  all cells in first sheet, 
  common column in second sheet,
  column you want in second sheet,
  optional output for missing values
  )

for example, in our students & fees case, we will use the below XLOOKUP function.

=XLOOKUP(C4:C43,
         Courses!B4:B15,
         Courses!D4:D15
         )

As demonstrated above, xlookup can automatically spill values for all the rows based on the common column you have specified. No need to individually write or drag the formulas. You also don’t need to “lock” your references with this style of formulas. Learn more about the spill functionality and dynamic array behavior of Excel here.

=XLOOKUP([@Course Name], courses[name], courses[fee])

What if I have more than one common column? (XLOOKUP multiple criteria)

Let’s say you have different fee per course based on the the student’s location (ex: In state is $600, out of state is $900). Something like this:

In such cases, your student’s data will also have both “course code” and “student type” columns. something like this:

As you can see, our XLOOKUP needs to check both of these columns to figure out the correct fee per row.

XLOOKUP with two sheets, multiple columns (step-by-step instructions)

Step 1: Identify common columns between sheets

In our case, the common columns are,

• Course Code (column C in sheet 1, column B sheet 2)
• Student Type (column D in sheet 1, column C in sheet 2)

Step 2: Write the multi-criteria XLOOKUP

Instead of looking up for a specific column value, we start the xlookup with 1 and construct a “boolean” checking array. The formula looks like this:

=XLOOKUP(1,
('Courses NEW'!$B$4:$B$27=Students!C4)*('Courses NEW'!$C$4:$C$27=Students!D4),
'Courses NEW'!$D$4:$D$27)

Formula Explanation:

• We start the lookup with 1. I will explain what this is in a second.
• The lookup array has multiple parts, one per common column. As we have two columns to match (course code and student type), we have two parts here.
  • Part1: (‘Courses NEW’!$B$4:$B$27=Students!C4) checks which courses in column B of sheet 2 (course code column) match with the course of current student record (column C of sheet 1)
  • Part 2: (‘Courses NEW’!$C$4:$C$27=Students!D4) does the same, but for student type
  • Part 1 * Part 2: when we multiply both of these checks, we end up with an array of 0s and 1s. for example, it will look like this: {0;0;0;0;0;1;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0;0}
• The return array is just the fee column of sheet 2
• As we are looking for 1, xlookup matches the 1 in Part 1*Part 2 and returns the corresponding fee.

Related: learn more about what and how of this boolean multiplication logic in Excel

Watch – XLOOKUP with multiple criteria explained quickly

Generic Formula Pattern for any number of common columns with XLOOKUP

Use this pattern and adjust everything as per your data to match any number of common columns

=XLOOKUP(
    1,
      (COLUMN 1 in second sheet = value 1 first sheet) * 
      (COLUMN 2 in second sheet = value 2 first sheet) * 
      (COLUMN 3 in second sheet = value 3 first sheet) * 
      (COLUMN 4 in second sheet = value 4 first sheet),
    COLUMN YOU WANT TO GET IN SECOND SHEET,
    OPTIONAL value for missing cases
  )
      

What if I have data in two separate workbooks (Excel files) instead of sheets

The process is exactly same as two sheets. You just need to keep both files OPEN for the XLOOKUP to work. If you close the second file (one with fees in this example), the formula in first workbook works as long as you don’t touch it or recalculate the workbook (F9). At that point it will throw an error and ask you to open the file.

Alternatives to XLOOKUP for combining data from two places

While xlookup is great, you can also use below alternatives to get data from another place.

• Use VLOOKUP to combine two sheets of data: You can use VLOOKUP (or even INDEX+MATCH) to combine data from two places. Read this article for the instructions on how to use VLOOKUP .
• Power Query for combining or merging data: Excel Power Query is another great way to combine data from two places. Refer to this video to learn more about power query for combining two sheets of data.
• Power Pivot to combine data from tables to make a single pivot: You don’t always have to combine data. You can keep things where they are and join tables via common column just like databases with Excel’s Power Pivot feature. This lets you calculate total fees or averages using pivot tables. Refer to this page for an introduction on how to use data model and power pivot feature of Excel.

My preferences:

For simple scenarios and quick analysis, I prefer using XLOOKUP or VLOOKUP to quickly combine data like this.

But if the data is coming from two separate files (workbooks or even sharepoint lists etc.), then I use Power Query. It gives me more flexibility and choices. Refer to my Power Query tutorial page for more spicy examples on what this powerful feature can do for you.

Bonus: XLOOKUP with two sheets: Sample workbook

If you need a hand with the formulas explained above, download my free XLOOKUP two sheets template and refer to the formulas in columns E & F. Let me know if you have any questions by leaving a comment.

Related Resources:

To learn more about the important Excel functions and concepts, refer to below articles & videos:

• How to use XLOOKUP function in Excel?
• How to use Power Query in Excel?
• How to use Excel Tables?
• Power Pivot in Excel – Beginner tutorial