Summary

One way to reorder columns in Excel is to use a formula that nests one XLOOKUP function inside another. In the example shown, The formula in G9 is:

=XLOOKUP(G8:J8,B4:E4,XLOOKUP(G5,E5:E15,B5:E15))

The result is a match on the value in G5, with all 4 fields in the order specified in the range G8:J8.

Generic formula

=XLOOKUP(neworder,oldorder,XLOOKUP(val,lookup,results))

Explanation 

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 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.

Dave Bruns Profile Picture

AuthorMicrosoft Most Valuable Professional Award

Dave Bruns

Hi - I'm Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.