make column reference based on cell value

Question

Create reference like, "V:V" or "T:T".

=(D3+L3)*INDEX(V:V,MATCH(H3,T:T,0))

I know how to use indirect to get D3+L3. 

=INDIRECT($Z$3&AE3)+INDIRECT($Z$6&AE3)

Z contains a letter value (a-z), AE contains a row number. 

What i need to know now is, how to make a column reference.

How can I create V:V ?

How can I create T:T ?

Answer 1

Hi JW

According to the logic of your formula =INDIRECT($Z$3&AE3)+INDIRECT($Z$6&AE3)

to get the result D3+L3

The values in the cells are the following

cell Z3="D"

cell AE3=3

cell Z6="L"

So to get the V:V and T:T

We would need to know the cell references to create the indirect formula.

Let's say, for example

Cell Z4 = "V"  and cell Z5="T"

Then

INDIRECT($Z$4&":" $Z$4 **)**Will return "V:V"

INDIRECT($Z$5&":" $Z$5 **)**Will return "T:T"

Then the formula part INDEX(V:V,MATCH(H3,T:T,0))

would be

INDEX(INDIRECT($Z$4&":" $Z$4 ),MATCH(H3,INDIRECT($Z$5&":" $Z$5 ),0))

The entire formula

**=(INDIRECT($Z$3&AE3)+INDIRECT($Z$6&AE3))***INDEX(INDIRECT($Z$4&":" $Z$4 ),MATCH(H3,INDIRECT($Z$5&":" $Z$5 ),0))

I hope this helps you.

Otherwise

Could you please, provide more and clearer details of your scenario, the expected results, and goals.

Preferably, provide a sharable link to a sample data, it would help us assist you better on the issue.

Regards

Jeovany

Answer 2

Hi JW,

Thank you for reaching out to us. I am Thuy, an Independent Advisor and Microsoft user like you.

For this task, if you want to create a reference for the column V and T, one way to do is formatting your data as a table, then replace table header as the column reference for each one in the formula of your use. For instance, Table1[<Header>] in which <Header> is the header name of column V or T.

https://support.microsoft.com/en-us/office/usin...

If that is not the case, could you share a sample data and what you expect to get as the outcome then it would help us assist you better on the issue.

Hope this helps and please let me know if you need more assistance.

Regards

Answer 3

Thanks!

My data is a range and I am not partial to tables. But, thanks for your assistance.

Answer 4

Thanks! 

When i make my first million.... I'm buying you a Krystal WITH cheese!

(...or White Castle burger if you prefer)