To transpose XLOOKUP results in Excel, wrap the XLOOKUP function in the TRANSPOSE function, like this: =TRANSPOSE(XLOOKUP(lookup_value, lookup_array, return_array)). This flips a horizontal row of returned values into a vertical column (or vice versa), which is useful when your lookup table is oriented differently from your output layout.
Why Transpose XLOOKUP Results?
XLOOKUP normally returns a single value, or when you give it a multi-column return array, it returns a row of values that spill horizontally across cells. But sometimes your data is stored in a vertical column and you need the results laid out horizontally, or your lookup table is horizontal and you need vertical results. Transposing solves this mismatch without manually copying and pasting with the transpose option.
For example, if you have a product ID in A2 and a horizontal table with product details in B1:F1 (headers) and B2:F2 (values), =XLOOKUP(A2, B1:F1, B2:F2) would return a single value (the first match). To get all details for that product in a vertical list, you would use =TRANSPOSE(XLOOKUP(A2, B1:F1, B2:F2)).
How TRANSPOSE Works with XLOOKUP
The TRANSPOSE function converts a horizontal array into a vertical array, and vice versa. When you combine it with XLOOKUP, the XLOOKUP part returns an array (a row of values), and TRANSPOSE reorients that array.
Key points:
- TRANSPOSE is an array function, but in Excel 365 and Excel 2021, you don’t need to press Ctrl+Shift+Enter; it spills automatically.
- The result must be placed in a range with enough cells to hold the transposed data. If the original return array has 5 columns, the transposed result will occupy 5 rows.
- If the XLOOKUP returns a single value, TRANSPOSE has no effect (it just returns that value).
Example: With product IDs in A2:A5 and a vertical table in D1:E5 (where D1:D5 are IDs and E1:E5 are prices), to get the price for the ID in A2 and display it horizontally, you would use =TRANSPOSE(XLOOKUP(A2, D1:D5, E1:E5)). This returns a single value, so it’s not useful. Instead, to return multiple columns horizontally, your return array must be horizontal to begin with.
Step-by-Step: Transpose a Horizontal Lookup to Vertical
Suppose you have a product lookup table with headers in B1:F1 (Product, Price, Stock, Rating, Supplier) and data in B2:F2. You want to look up a product name in A2 and list all its details vertically in cells A5:A9.
- In cell A5, enter the formula:
=TRANSPOSE(XLOOKUP(A2, B1:F1, B2:F2)) - Press Enter. Excel spills the results down from A5 to A9.
- If you don’t see all results, check that there are no cells blocking the spill range (e.g., text in A6).
This works because XLOOKUP returns an array of five values (one for each column), and TRANSPOSE turns that row into a column.
Transpose a Vertical Lookup to Horizontal
If your lookup table is vertical (e.g., IDs in A2:A10, details in B2:B10 for one product), and you want to return multiple columns horizontally, you need to restructure your return array. XLOOKUP can return multiple columns if you specify a multi-column range, but that range must be contiguous. For example, with IDs in A2:A10 and details in B2:D10 (Name, Price, Stock), to look up ID in E2 and return all three details horizontally, use:
=XLOOKUP(E2, A2:A10, B2:D10)
This spills horizontally across three cells. If you need it vertical, wrap with TRANSPOSE: =TRANSPOSE(XLOOKUP(E2, A2:A10, B2:D10)).
Common Problems and Solutions
Here are frequent issues when transposing XLOOKUP results:
- #VALUE! error: This happens if the return array is not a single row or column, or if TRANSPOSE is used on a single value (no error, but no effect). Check that XLOOKUP returns an array.
- #SPILL! error: The transposed result would overwrite existing cells. Clear the cells in the spill range or move the formula.
- Results not updating: If you change the lookup value, the transposed results update automatically because it’s a dynamic array formula.
- Transpose not available in older Excel: XLOOKUP and TRANSPOSE (as a dynamic array) require Excel 365 or Excel 2021. In older versions, you’d need to use INDEX/MATCH with TRANSPOSE (array-entered with Ctrl+Shift+Enter).
Alternatives to TRANSPOSE with XLOOKUP
If you don’t want to use TRANSPOSE, you have other options:
- Use INDEX/MATCH: For a single value, you can use INDEX with MATCH to return a value from a specific column, then drag the formula. This doesn’t require dynamic arrays.
- Copy and Paste Special > Transpose: If you only need a one-time transposed copy, copy the XLOOKUP results and paste with the Transpose option (right-click > Paste Special > Transpose). This creates static values, not formulas.
- Use HSTACK or VSTACK: In Microsoft 365, you can combine arrays with HSTACK (horizontal) or VSTACK (vertical) to build custom layouts, but they don’t directly transpose.
Frequently Asked Questions
Can I transpose XLOOKUP results in Excel for Mac?
Yes, TRANSPOSE works the same in Excel for Mac. The formulas are identical, and dynamic arrays are supported in Excel for Microsoft 365 on Mac. The only difference is keyboard shortcuts (e.g., Cmd+Enter instead of Ctrl+Shift+Enter for legacy array formulas).
Why does my TRANSPOSE(XLOOKUP()) return a single value instead of an array?
This happens when the return_array in XLOOKUP is a single cell or a single row/column that contains only one value. For example, if you use =TRANSPOSE(XLOOKUP(A2, B1:F1, B2)), the return array is just B2, so TRANSPOSE has nothing to flip. Use a range that includes multiple cells.
Can I use TRANSPOSE with XLOOKUP to return multiple columns from a vertical lookup?
Yes, but the return array must be a multi-column range. For example, =TRANSPOSE(XLOOKUP(A2, D1:D5, E1:G5)) returns the values from E, F, and G for the matching row, transposed into a vertical stack. This works because XLOOKUP returns a row of three values, and TRANSPOSE flips it to a column.
Does TRANSPOSE work with XLOOKUP in Excel 2019?
No, XLOOKUP is not available in Excel 2019. You would need to use INDEX/MATCH with TRANSPOSE, entered as an array formula (Ctrl+Shift+Enter). For example, =TRANSPOSE(INDEX(B2:D10, MATCH(E2, A2:A10, 0), 0)).
How do I transpose XLOOKUP results without using TRANSPOSE?
You can use the Paste Special > Transpose feature after copying the spilled results. However, this creates static values. For a dynamic solution, you could use a combination of INDEX and SEQUENCE to reorient the array, but TRANSPOSE is the simplest.
Final Tips for Using TRANSPOSE with XLOOKUP
When combining these functions, always check the orientation of your data and the desired output. Remember that TRANSPOSE only changes the direction of an array; it does not change the values themselves. Also, be aware that dynamic array formulas will spill, so ensure your worksheet has enough empty space. If you need to transpose a large dataset, consider using Power Query or a pivot table, but for quick lookups, TRANSPOSE(XLOOKUP()) is an elegant solution.
Related
- Excel Staff Schedule Template: Free Options & How to Build One
- Excel Spreadsheet Templates for Tracking Time
- Excel Spreadsheet Timesheet Template: How to Make One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel: Test If a Cell Contains Specific TextIn Excel for Microsoft 365, test whether a cell contains specific text with =ISNUMBER(SEARCH("apple",A2)). It returns TRUE when…
- How to Multiply in Excel in the Same Cell: FormulasTo multiply in Excel and show the answer in a cell, select that cell, type a formula beginning…
- Excel Time Math: Formulas for Adding and Subtracting HoursExcel time math treats times as fractions of a 24-hour day: subtract an earlier time from a later…
- Capitalize in Excel Without a Formula: 3 Easy MethodsTo capitalize text in Excel without a formula, use Data > Flash Fill: type the capitalization you want…
- WEEKDAY Function in Excel: Find the Day of the WeekExcel’s WEEKDAY function returns a number for the day of the week represented by a date. If cell…
- SPLIT in Snowflake SQL: Functions, Examples, and TipsIn Snowflake SQL, SPLIT(string, delimiter) divides a string wherever a literal delimiter occurs and returns the pieces as…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.