Dynamic Column reference

[daniel95]

Hey everyone,

I'm trying to write a formula where I can dynamically give a column reference based on the selection criteria.

For example, if the column selected is Alpha, the cell reference should be "C2" in the data set sheet

If the column selected is Beta, the cell reference should be "D2" in the data set sheet

Output (Sheet 1) -

Data set (Sheet 2)-

 

[danielphayward]

=CELL("address",INDEX(A2:O2,1,MATCH(D11,A2:O2,0)))

Something along those lines should do it. A2:O2 is your header row - D11 is your criteria.

 

[Fluff]

How about

=INDEX(Sheet2!C2:E2,MATCH(C1,Sheet2!C1:E1,0))

 

[danielphayward]

@Fluff, doesn't that give the value, not reference? Maybe I misunderstood his question.

 

[daniel95]

How about

=INDEX(Sheet2!C2:E2,MATCH(C1,Sheet2!C1:E1,0))

Awesome! This solves it!

 

[Fluff]

Glad we could help & thanks for the feedback.