Excel Table Fourmulas Question

[markpeegray]

I am developing a worksheet template for my group.  The user will copy some data from our ERP and paste into a worksheet that has a table and some formulas.  The formulas all reference the column names (fields) not cell references.

The problem is that some users have columns in different order in the ERP when they copy paste.  The column names are the same, but can be in different locations.  When the user pastes data, the formulas "update" because the column names (fields) changed (just like if you rename a field).

I would like to lock the field names in the formula so excel will find it, not just use whatever name is at the top of the column.

Example;

Full name | city, state | last | first | other data | city | state |other data |
==========|=============|======|=======|============|======|=======|============|
Formula   | formula     | data | data  | data       | data | data  | data      |

My table is set up like that

Some users have columns in different order and I want the formula to "find" the column that has "city".

I can set this up with INDEX and MATCH, but I'm hoping not too (the example above is very simplified).

I don't want to ask users to rearrange the columns or all use the same view.

[#2]

@markpeegray 

If the column names are the same you could use XLOOKUP (or INDEX/MATCH) to select the column by header name. In general it's better to have concrete sample - what is input and which formulae you use for output.

Another point, instead of copy/paste from ERP perhaps Power Query could work to pickup the data.

[#3]

Thanks for your help. I'm trying to avoid using index and match, but maybe I can work something in that way without too much trouble.

[#4]

@markpeegray 

If you share sample file with desired output perhaps it could be another suggestion. But again, Power Query from ERP could be the solution.