Solved: How to use XLOOKUP with multiple criteria to return a dynamic array across non-adjacent columns in Excel

📌 The Problem Explained

Imagine you manage a large master inventory spreadsheet. You need to pull data based on two specific criteria (e.g., Department and Supplier Name). However, the information you need to retrieve—such as Unit Price and Last Restock Date—resides in columns that are not next to each other.

Standard XLOOKUP can handle multiple criteria using boolean logic, and it can return a dynamic array if the return range is a contiguous block. However, if you need to skip over middle columns, a standard XLOOKUP will return everything in between. Most users resort to writing multiple formulas, which is inefficient and prone to errors when the data scales.

💡 The Quick Solution

To solve this, combine XLOOKUP with the CHOOSECOLS function. Use the following formula structure:

=CHOOSECOLS(XLOOKUP(1, (Range1=Criteria1)*(Range2=Criteria2), TableRange), ColIndex1, ColIndex2)

Example: To find a row where Column A is “North” and Column B is “Widgets,” and return columns 3 and 5 from that row: =CHOOSECOLS(XLOOKUP(1, (A2:A10="North")*(B2:B10="Widgets"), A2:E10), 3, 5)

🛠️ Step-by-Step Breakdown

To implement this professionally, follow these steps to build the nested formula:

Step	Action	Logic Behind It
1	Define your criteria logic	Use (Range1=Criteria1)*(Range2=Criteria2). This creates an array of 1s (True) and 0s (False).
2	Set the XLOOKUP value	Set the lookup_value to 1. XLOOKUP will search the array created in Step 1 for the first “True” match.
3	Select the Return Array	Select the entire range of your table (e.g., A2:Z100). This allows the formula to “see” all potential columns.
4	Wrap in CHOOSECOLS	Wrap the entire XLOOKUP function inside =CHOOSECOLS( [XLOOKUP], Col#1, Col#2 ).
5	Specify Column Indices	Enter the relative column numbers you want to display (e.g., 1, 4, 7) separated by commas.

Detailed Implementation via UI:

1. Click on the cell where you want the results to start (ensure there is empty space to the right for the dynamic array to “spill”).
2. Go to the Formula Bar and type the = sign.
3. Enter the CHOOSECOLS function first, as it acts as a filter for the columns.
4. Inside it, nest the XLOOKUP using the boolean multiplication method (*) for your multiple criteria.
5. Close all parentheses and press Enter.

⚠️ Common Mistakes to Avoid

• Array Dimension Mismatch: Ensure that your lookup ranges (e.g., A2:A10 and B2:B10) are the exact same height. If one is longer than the other, the formula will return a #N/A error.
• The Spill Error: Since this is a dynamic array formula, it will “spill” results into adjacent cells. Ensure those cells are empty; otherwise, Excel will throw a #SPILL! error.
• Version Compatibility: The CHOOSECOLS function is only available in Excel for Microsoft 365 and Excel for the Web. If you are using Excel 2019 or 2021, you will need to use the INDEX and MATCH combination with an array constant {1, 0, 1} which is significantly more complex.