tables and returning data
Posted by Andy on September 05, 2001 10:35 PM
Cell A1 is headed up with the current month eg Sept
Cell B1 - M1 are the headings July thru to June
Rows 2-10 under each column B - M are filled with numbers
In Cells A2 : A10 I want a formula that will return the values in the column that matches the heading in Cell A1
I'm sure this involves hlookup but am struggling with it
Posted by Aladin Akyurek on September 05, 2001 11:27 PM
Andy,
If A1 to M1 contains just month names (that is, no dates) or dates whose day and year components are the same (that is, 1-Sep-01,1-Jun-01,1-Jul-01,etc), then
in A2 enter either: =INDIRECT(ADDRESS(ROW(),MATCH($A$1,$B$1:$M$1,0)+1)) [ copy down as far as needed ]
or:
=INDIRECT(ADDRESS(ROW(),SUMPRODUCT(($B$1:$M$1=$A$1)*COLUMN($B$1:$M$1)))) [ copy down as far as needed ]
In case you have dates whose day and/or year components are different (that is, 1-Jun-01,7-Jul-01,etc), use:
=INDIRECT(ADDRESS(ROW(),SUMPRODUCT((MONTH($B$1:$M$1)=MONTH($A$1))*COLUMN($B$1:$M$1)))) [ copy down as far as needed ]
Aladin
==========
Posted by Tom Urtis on September 05, 2001 11:32 PM
Here are the formulas you need
Andy,
Using HLOOKUP, here are your formulas.
In cell A2 enter:
=HLOOKUP($A$1,$B$1:$M$10,2,0)
In cell A3 enter:
=HLOOKUP($A$1,$B$1:$M$10,3,0)
In cell A4 enter:
=HLOOKUP($A$1,$B$1:$M$10,4,0)
In cell A5 enter:
=HLOOKUP($A$1,$B$1:$M$10,5,0)
In cell A6 enter:
=HLOOKUP($A$1,$B$1:$M$10,6,0)
In cell A7 enter:
=HLOOKUP($A$1,$B$1:$M$10,7,0)
In cell A8 enter:
=HLOOKUP($A$1,$B$1:$M$10,8,0)
In cell A9 enter:
=HLOOKUP($A$1,$B$1:$M$10,9,0)
In cell A10 enter:
=HLOOKUP($A$1,$B$1:$M$10,10,0)
Tom Urtis
