Index / Match based on column header

I have a file with two sheets:

• Sheet A has the raw data.
• Sheet B is a working sheet, where I add data from Sheet A.

The column with employee ID's is static. The other columns are dynamic. Meaning the employee names are currently in column I, but this might change to column K tomorrow. It will constantly change, based on the changing needs in the upcoming days.

Column H has the Employee ID's and will not change.

An example formula is as follows.

Please Login or Register  to view this content.

Is it possible to replace the "[Employee Name]" part and have it refer to a cell value? In this case, it would be the value in cell I1, as the column header is "Employee Name"?

Obviously, is there's a more logic solution, I'm also happy to hear. Many thanks!

If your data is an Excel Table then the name of the column should be all you need, I think. If it's not, then you should be able to do a MATCH for the column heading.

If you need more detail, please upload a sample workbook so we can see the structure a bit more clearly.

Thanks for the fast response, Nick.

Attached an example sheet. The raw data can be found in the sheet "RAW_DATA" and is stored in an Excel table. The data in the Working Sheet is not. Right now, in the Working Sheet, the Job Title is loaded into column B. Instead of the Job Title, I might want to load the Location instead in column B. Instead of manually adjusting the formulas, I'd rather have the formula refer to the column header in the Working Sheet. For example, if would change the column header (in cell B1) to "Location", then it would automatically show the Locations instead of the Job Titles.

Hope this clarifies what I'm looking for

In B2 of WORKING_SHEET, try:

=INDEX(RAW_DATA_TABLE[#All],MATCH($A2,RAW_DATA!$A:$A,0),MATCH(B$1,RAW_DATA!$1:$1,0))

Originally Posted by nick.williams

In B2 of WORKING_SHEET, try:

=INDEX(RAW_DATA_TABLE[#All],MATCH($A2,RAW_DATA!$A:$A,0),MATCH(B$1,RAW_DATA!$1:$1,0))

Awesome! This is exactly what I was looking for. Thanks a ton, Nick. Much appreciated!

You're welcome