index/match based on a dynamic column header

[JGARDNER-AIT]

Hello - Is there a additonal formula I can add to an index/match formula to pull a value based on a dynamic column header name?

The indexed raw data can grow and shrink by column depending on project and was looking to automatically code in the coumn header vs hard coding the column number which will not work in a dynamically indexed raw data file.

To further explain what I'm looking for.
I have a part number 123456 in Cell A2 on sheet 1.
I want it to find in sheet 2 the 123456 in fixed column "b" and find a defined column header "description" and then intersect and find the value under the "description" column.
The column "description" could be the 2nd column in the index or the 50th column in the index.

Anyones help would be greatly appreciated.

Thanks - Josh

 

[MrKowz]

Try:

=INDEX(Sheet2!A:Z,MATCH(A1,Sheet2!B:B,0),MATCH("description",Sheet2!1:1,0))

Adjust the INDEX Array (A:Z) to fit your data accordingly.

 

[JGARDNER-AIT]

Thank you - Works perfect!

Try:

=INDEX(Sheet2!A:Z,MATCH(A1,Sheet2!B:B,0),MATCH("description",Sheet2!1:1,0))

Adjust the INDEX Array (A:Z) to fit your data accordingly.

 

[jumbledore]

Try:

=INDEX(Sheet2!A:Z,MATCH(A1,Sheet2!B:B,0),MATCH("description",Sheet2!1:1,0))

Adjust the INDEX Array (A:Z) to fit your data accordingly.

I know this is as on old post but what if the column header for column B in Sheet2 was also dynamic?

 

[jumbledore]

I know this is as on old post but what if the column header for column B in Sheet2 was also dynamic?

I have tried it with "indirect" but indirect doesn't work with closed external files

 

[rdegrasse]

Try:

=INDEX(Sheet2!A:Z,MATCH(A1,Sheet2!B:B,0),MATCH("description",Sheet2!1:1,0))

Adjust the INDEX Array (A:Z) to fit your data accordingly.

I have a similar type question. I have a column in a set of that comes to use dynamically (can be in a different column position each time the data is received). I am using the following formula:
=INDEX('Exported Data'!$A$1:$ZZ$800,MATCH($B10,'Exported Data'!$AG:$AG,0),MATCH('Condition Codes'!B$1,'Exported Data'!$1:$1,0)) where the data comes in the worksheet "Exported Data" and in the formula 'Exported Data'!$AG:$AG is the column that comes in dynamic. The header of the column is always the same "Rm # / Area".

How do I replace 'Exported Data'!$AG:$AG in my formula so that it looks for and assigns the column with the header "Rm # / Area"?