Dynamically reference to column with data

[Otchoo2]

Hi,

I am trying to reference to a column in a different sheet, like this. =Order!C1.:.C9999
But how can i do that dynamically? I tried it with adress and match to find the right header and tried to combine this. But i cant get it to work.

The reason i try to do this is the data that i am looking for is sometimes in Column A and sometimes it is in a different Column.

 

[HighAndWilder]

Hi,

I am trying to reference to a column in a different sheet, like this. =Order!C1.:.C9999
But how can i do that dynamically? I tried it with adress and match to find the right header and tried to combine this. But i cant get it to work.

The reason i try to do this is the data that i am looking for is sometimes in Column A and sometimes it is in a different Column.

Will the column always have the same column heading?

 

[Otchoo2]

Hi Thx for the reply.

The header will always have the name "Item"

 

[HighAndWilder]

Hi Thx for the reply.

The header will always have the name "Item"

Are you able to put the data in the Orders sheet into a table as the position of the column is not important then?

 

[Otchoo2]

Hi. A Table isn’t an option in this case.

 

[Fluff]

How about

=LET(d,Order!A1.:.Z9999,INDEX(d,,XMATCH("item",TAKE(d,1))))

 

[Otchoo2]

How about

=LET(d,Order!A1.:.Z9999,INDEX(d,,XMATCH("item",TAKE(d,1))))

Yes Thats it. Thank you for the help.

 

[Fluff]

You're welcome & thanks for the feedback.