XLOOKUP from table where the looked up columns may move

Hi

I am stuck trying to get an XLOOKUP formula to work when looking up from a table where the columns may change order.

The table which has the data for the lookup has many rows and columns and is updated with copy and paste as values from a master version. The master version cannot be linked directly due to sensitive information. The master version may change at any time, moving columns around. This is creating havoc with my current XLOOKUP formula!

I have attached a spreadsheet with an example of the problem I am trying to solve.

Any help would be much appreciated to fix the XLOOKUP formula, or use another function which is better for this job!

Many thanks

Kev

Maybe use INDEX and MATCH, where the MATCH function locates the column by looking at the headings.

Hope this helps.

Pete

N4=XLOOKUP(L4,INDEX(Table1,,MATCH(L3,Table1[#Headers],0)),INDEX(Table1,,MATCH(M3,Table1[#Headers],0)))

Many thanks CARACALLA


Unfortunately when I copy and paste the data from the table in columns O to X into columns A to X, in order to replicate how the data is updated, the formula no longer works.

I really need to the formula to continue to work after the data is updated.

Any help is much appreciated

Best Wishes

Kev

Hi Glenn

Many thanks but this didn't really fix it because I only want to look up from the table in the top left of the sheet.


The other table (columns O to X) was only there to use as an example of the table I copy data from (which is actually in a different spreadsheet).

BUT

Combining your formula and the formula from CARACALLA, I think I have it sorted using:


=XLOOKUP(L4,INDEX(A1:J8,,MATCH(L3,A1:J1,0)),INDEX(A1:J8,,MATCH(M3,A1:J1,0)))

I attached the solution in case anyone else has this problem. The updated version has the example tables to copy and paste from moved to rows 24 onwards.

Thank you all for your help!

Many thanks Pete!

Hi Glenn

In reality, the table in column O to X will not be there.

If I delete that data, the formula no longer works - it doesn't get the info from the table in columns A to J.

I have altered the spreadsheet so that the example tables to copy from are on a different sheet to the final table to be looked up.
There are two example tables to copy across where the columns are in a different order

Best Wishes

Kev

Hi Glenn

Sorry - I thought I was clear on this by stating that the columns may move in the title of this thread, and by stating 'The master version may change at any time, moving columns around' in my first post.

Either way, many thanks for your help in getting to the solution.

Best Wishes

Kev