Dynamic in finding column name in formula

[harinsh]

Hi Team,

I need some option or solution to my query. I have dataset which define with table and given table name and total column consists of 10 and this is not fixed tomorrow when I get fresh data the number of columns may get increase to 11 or 12.

I created few formulas based on the table (within the table at beginning of the column) and when I paste the new data columns also might get change for example in column "B" if you have EMP ID tomorrow it might be in column "C".

If I define EMP ID in formula it will only pick always column B not EMP ID.. It should automatically pick the column based on the column name not column number.

Is there any work around for this. Thank you,

 

[Joyner]

Hello you haven't described enough to give an actual solution but you can use MATCH to find the column heading, for example:

=MATCH("EMP ID",A1:Z1,0)

that will give you the relative position of the column and you can use that in other functions like INDEX to do things.

 

[harinsh]

Hi Joyner,

Thanks for your reply. I understand the formula to find out the column name. But my question, I have 3 formulated columns beginning of the table and using couple of calculated formulas like "Sum if " & Match, Vlookup etc., when I refer the column name it will get static and tomorrow my data may not contain name column name in column 2 it vary from day by day. (Today column D contains EMP ID but tomorrow EMP ID will be at column Z)

I want not to change the column name in the formula irrespective of data change but the column name will not change only position of column names will change.

 

[Joyner]

I'm not following you, there is nothing "static" about using MATCH, if you care to post an example I can show you how to use INDEX and MATCH for example so that SUMIFS and VLOOKUP will work regardless of where the column is in your data.