# XLOOKUP rearrange columns

XLOOKUP can be used to reorder columns, by nesting one XLOOKUP inside another. In the example shown, The formula in G9 is:

Which returns a match on the value in G5, with all 4 fields in a different sequence.

*beta function*, available only through the Office Insiders program.

### How this formula works

This formula uses XLOOKUP twice, by nesting one XLOOKUP inside another. The first (inner) XLOOKUP is used to perform an exact match lookup on the value in G5:

XLOOKUP(G5,E5:E15,B5:E15)

- The
*lookup_value*comes from cell G5 - The
*lookup_array*is E5:E15 (codes) - The
*return_array*is B5:E15 (all fields) - The
*match_mode*is is not provided and defaults to 1 (exact match) - The
*search_mode*is not provided and defaults to 1 (first to last)

The result is a match on "AX-160", returned as an array of all four fields in the original order:

{160,130,60,"AX-160"}

This result is delivered directly to the second (outer) XLOOKUP as the return array argument. The lookup value is provided as a range representing the *new* order of fields, and the lookup array is the range containing the *original* field name order.

=XLOOKUP(G8:J8,B4:E4,{160,130,60,"AX-160"})

- The
*lookup_value*is the range G8:J8 (new field order) - The
*lookup_array*is the range B4:E4 (old field order) - The
*return_array*is the result from the first XLOOKUP

This is the tricky bit. We are passing in multiple lookup values, so XLOOKUP internally will calculate multiple match positions. For each value in the *new field order range*, XLOOKUP will find a position inside the *old field order range* and use this position to fetch a value from the return array (the values returned by the first XLOOKUP function). The result is the original lookup result with fields arranged in the new order.

## Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.