Variable Column Lookup

[Curly]

I have three columns to the left column K each has a header with the year
number. 2008, 2009 and 2010. Underneath each of the three columns I have
numerical data. On the same header row I have a drop down list in K1. So
when I pick 2009, I want the calculations in K2, K3 downward to use the
corresponding data in column that has the matching year.

2008 2009 2010 2009 (drop down)
2 3 6 3*10

If I choose the drop down of 2008 then the formula should be 2*10
If I choose the drop down of 2010 then the formula should be 6*10

The actual formula is much more complicated involving INDEX/MATCH, but I
think this should relay the idea.

Thanks for any help.

 

[Dave Peterson]

I'd start on Debra Dalgleish site:
http://www.contextures.com/xlFunctions03.html (for =index(match()))
and
http://contextures.com/xlFunctions02.html#Trouble

 

[Jim Thomlinson]

In K2 add this and drag it down...

=index(H2:J2, match($K$1, $H$1:$J$1, 0)) * 10

 

[Curly]

Hi Jim,

Thank you for responding and quickly. Your solution worked. Thank you.

In my real application, the headers are actually text. The drop down says
Net 2009 or Net 2010. I can work with that by modifying your solution to ...
MATCH(VALUE(RIGHT($K$1,4)),.. However, I do not know how to modify the other
parts when the other three columns also have text. They are actually labled
"Net Issue 2008", "Net Issue 2009" etc. Could you help with that
configuration?

 

[Curly]

Hi Dave,

Thank you for the references at the Dalgleish site. I have seen them and
are somewhat familiar with the functionality of the lookups. However, from
that site and others, I could not see how to extend those principles to my
situation - especially when text is involved as my second part of the
question I posed above.

 

[Jim Thomlinson]

Perhaps this will work as a fix.

=index(H2:J2, match("Net Issue " & right($K$1, 4), $H$1:$J$1, 0)) * 10

I have just concatenated the words Net Issue to the front of the year chosen
in K1

 

[Curly]

That did it! Very clever. I wasn't sure if there was such a thing as RIGHT,
LEFT, MID on ranges something like RIGHT(H2:J2,4).

Your assistance is much appreciated.

Curly

 

[Jim Thomlinson]

You can apply functions to ranges of cells with Array formulas but it's not
worth it in this case...