XLOOKUP Rearrange Columns: How To Reorder Results

XLOOKUP can rearrange columns by returning values from a different column than the one you searched, and you can control the output order by listing multiple return columns or by using the return array argument. This lets you reorder the lookup results without moving the source data. For example, you can look up an employee ID and return the employee’s last name, then first name, even if the source table lists first name before last name.

Why Rearrange Columns with XLOOKUP?

You might need to reorder columns for a report, a dashboard, or a data import that expects a specific column order. Rather than rewriting the source data or using multiple VLOOKUP formulas, XLOOKUP can return columns in any order in one formula. This is especially useful when the source table is shared or you cannot add helper columns.

For example, a sales report might need columns in the order: Customer Name, Order Date, Total, Salesperson. But your raw data has Salesperson first, then Customer, then Total, then Order Date. XLOOKUP with HSTACK can produce the desired order without touching the original data.

How to Choose the Right Method

There are several ways to reorder columns with XLOOKUP:

• Single return column – Use when you only need one column. No rearrangement needed.
• Multi-column return with HSTACK – Use when you have Excel 365 and want to combine columns in any order. This is the cleanest method.
• CHOOSE with array constant – Works in older versions (Excel 2019 and earlier) that don’t have HSTACK. You can specify column indices in the order you want.
• Multiple XLOOKUPs – Use separate XLOOKUP formulas for each column you need, each returning a single column. This is simple but may be less efficient if you need many columns.

For most users, HSTACK is the easiest to read and maintain. If you’re on an older version, CHOOSE is a reliable alternative.

Common Problems and Pitfalls

When rearranging columns with XLOOKUP, you might encounter these issues:

• #VALUE! error – Usually occurs when the return array dimensions don’t match the lookup array. Ensure the number of rows in the return array equals the number of rows in the lookup array.
• Spill range overlap – If you place the formula where it doesn’t have enough space to spill, you’ll get a #SPILL! error. Clear the adjacent cells or move the formula.
• Order of columns in HSTACK – Double-check the order you list columns. It’s easy to accidentally swap two columns.
• Using CHOOSE with column numbers – The column numbers refer to the order you list them in CHOOSE, not the original table. For example, CHOOSE({1,2}, C:C, B:B) returns C first, then B.
• Single cell vs. spilled array – If you only want one value, wrap the formula in INDEX or use the @ operator to return the first value.

XLOOKUP vs. VLOOKUP for Reordering

VLOOKUP can only return a column to the right of the lookup column, and it always returns a single column. To reorder, you would need multiple VLOOKUPs or a combination of MATCH and INDEX. XLOOKUP is more flexible because it can look left or right, and it can return multiple columns at once. This makes XLOOKUP the superior choice for rearranging columns, especially in dynamic array Excel.

Alternatives to XLOOKUP for Reordering

If you don’t have XLOOKUP (e.g., Excel 2016 or earlier), you can use INDEX and MATCH to reorder columns. For example, to return the Price, Stock, and Name for a product ID, you could use:

=INDEX($A$2:$D$100, MATCH(101, $A$2:$A$100, 0), {3,4,2})

This returns the third, fourth, and second columns of the matching row. In older Excel, you might need to enter this as an array formula (Ctrl+Shift+Enter).

Frequently Asked Questions

Can XLOOKUP return columns in a different order?

Yes, by using the return array argument with HSTACK or CHOOSE, you can specify the columns in any order. For example, =XLOOKUP(A2, B:B, HSTACK(D:D, C:C)) returns column D first, then column C.

Why is my XLOOKUP returning #VALUE! when I try to reorder?

This usually happens when the return array has a different number of rows than the lookup array. Ensure that all columns in the HSTACK or CHOOSE have the same number of rows as the lookup range.

Does XLOOKUP work in Excel 2019?

XLOOKUP is available in Excel 2021 and Microsoft 365, but not in Excel 2019. In Excel 2019, you can use INDEX/MATCH or CHOOSE with array formulas to achieve similar results.

Can I use XLOOKUP to reorder columns in Google Sheets?

Google Sheets has its own XLOOKUP function (as of 2022), but it does not support HSTACK. You can use { } array literals to combine columns: =XLOOKUP(A2, B:B, {D:D, C:C}). This works similarly.

How do I return just one column from a rearranged XLOOKUP?

Wrap the XLOOKUP in INDEX: =INDEX(XLOOKUP(...),1,1) for the first column, or use @ to get the first value if you’re in a single cell. Alternatively, use a separate XLOOKUP for each column.

What if I need to reorder columns and also filter out some rows?

You can combine XLOOKUP with FILTER (in Excel 365) to first filter the data, then reorder columns. For example: =XLOOKUP(A2, FILTER(B:B, C:C>10), HSTACK(D:D, E:E)) – but ensure the filtered arrays have the same dimensions.

Related

• Add a Game Overlay in Streamlabs Desktop
• HP Inkjet Printer: Do You Need Both Cartridges to Print?
• Mouse Double Click? Fix It in 5 Minutes