How to automatically reorder columns in a custom order in a google sheet?

Question

Let's say I have 3 columns: Name;Age;Gender, with X rows. I want my data to always be in this order. Imagine I have an input where I don't control the input's columns' order. It can be Age;Name;Gender, Gender;Age;Name, etc, with X rows. I have this data in a sheet, and want my 'sorted data' in another sheet, where the rows stay in the same order but the columns are sorted on the order I want (here: Name;Age;Gender).

How would you do that?

I have tried different approaches with LOOKUP but it's time consuming, it takes some time to calculate.I have tried different approaches with LOOKUP but it's time consuming, it takes some time to calculate.

Edit: Here is an example of the expected output : https://docs.google.com/spreadsheets/d/151EhdY7u8OfMB9Jk0c_Qm5CBHFodiLSFY24poweB5HQ/edit#gid=1724235235

Answer 1

Assuming that the column names "Name", "Age", "Gender" are in Sheet1!A1:C1 in some order, you can sort the columns Sheet1!A1:C in a standard order like this:

=map( 
  { "Name", "Age", "Gender" }, 
  lambda( 
    columnName, 
    filter(Sheet1!A1:C, Sheet1!A1:C1 = columnName) 
  ) 
)

In the event your spreadsheet locale uses commas , as decimal separators, you will have to use semicolons ; as formula argument separators and backslashes \ as { array expression } horizontal separators, like this:

=map( 
  { "Status" \ "Indexability Status" \ "Indexability" }; 
  lambda( 
    columnName; 
    filter(Raw_Data!A1:BG; Raw_Data!A1:BG1 = columnName) 
  ) 
)