Formula help for tables that columns may change positions

[Row89]

Hi

I have created the below formula in column S (report number) to bring through a report number.

=VLOOKUP(Table_query__1[Title],Lists!Q:R,2,FALSE)

The whole data table in Table_query_1 is data that will be pasted in every time I do a new report. This is data exported from MS lists that has the same column header names, however the column order may change (this is unavoidable). So [Title] in Table_query_1 may end up in column U, W or X etc when pasted in. 

The "Lists" in column Q match the names in [Title] (there's a total of 177 lines) and column R is the report number I need to bring through that's based off column Q.

I have just tested pasting in new data where the [Title] column was not in the same column and it has thrown my formula out.  How can I avoid this from happening? I've looked into doing a match and index, however I'm finding it difficult to navigate it.

[#2 · Accepted solution]

@Row89 

Hello, try this:

=XLOOKUP(Q7,Table1[Title],CHOOSECOLS(Table1,MATCH("Report N.",Table1[#Headers],0)))

You will only have to write the name of the column header of the values you want to return. Then, even if changes in columns are made, it will always find the value to return.

Attaching a sample document for you as well.

Martin

[#3]

thank you! that worked perfectly.

I need to do another one - I've got a cell named "Phase No" in I3 that I want to bring through all the column data into I4:I180 from a table that has the header name "Phase No". Is it an issue that the data in I3 isn't a table? I'd prefer not to change it to a table if possible, however the table that I'm pulling the data from needs to stay as a table.

[#4]

@Row89 

Glad it worked.

You can perfectly do that. It does not really matter if you are working with data from a table and data without a table. The formulation should work as well.

[#5]

@Row89 

If you use Power Query to get initial data from MS list, entire job could be done with Power Query without additional formulae.