Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return a value from a table using Column and row names as input?
What cellformula, using column and row names, return a specific value from a
table like below? (I cant use the INDEX function since I dont know the column or row numbers in the actual table, only the names) A B C D F 1 Week 42 Week 43 Week 44 Week 45 2 Company 1 67% 71% 69% 81% 3 Company 2 45% 78% 79% 73% 4 Company 3 88% 67% 86% 74% 5 Company 4 97% 56% 77% 92% 6 Company 5 87% 55% 82% 69% |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return a value from a table using Column and row names as input?
Hi
Use the MATCH function as well as INDEX. =INDEX($A$1:E$6,MATCH(Name,$A$A,0),MATCH(week,($1: $1,0)) Substitute the word Name and Week with the cell references holding the Company and Week Number or insert their text as "Company2" and "Week 44" for example. -- Regards Roger Govier "Olle" wrote in message ... What cellformula, using column and row names, return a specific value from a table like below? (I cant use the INDEX function since I dont know the column or row numbers in the actual table, only the names) A B C D F 1 Week 42 Week 43 Week 44 Week 45 2 Company 1 67% 71% 69% 81% 3 Company 2 45% 78% 79% 73% 4 Company 3 88% 67% 86% 74% 5 Company 4 97% 56% 77% 92% 6 Company 5 87% 55% 82% 69% |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return a value from a table using Column and row names as inpu
Thank you very much for your quick response!
I tried your suggested formula and with some minor adjustments it almost got it to work. Using the below formula I don't get the "Company 5, Week 45" value, I get the "Company 4, Week 44" instead. How can that be? Solution? =INDEX($A$1:E$6;MATCH("Company 5";A2:A6;0);MATCH("Week 45";B1:E1;0)) Regars Olle "Roger Govier" wrote: Hi Use the MATCH function as well as INDEX. =INDEX($A$1:E$6,MATCH(Name,$A$A,0),MATCH(week,($1: $1,0)) Substitute the word Name and Week with the cell references holding the Company and Week Number or insert their text as "Company2" and "Week 44" for example. -- Regards Roger Govier "Olle" wrote in message ... What cellformula, using column and row names, return a specific value from a table like below? (I cant use the INDEX function since I dont know the column or row numbers in the actual table, only the names) A B C D F 1 Week 42 Week 43 Week 44 Week 45 2 Company 1 67% 71% 69% 81% 3 Company 2 45% 78% 79% 73% 4 Company 3 88% 67% 86% 74% 5 Company 4 97% 56% 77% 92% 6 Company 5 87% 55% 82% 69% |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return a value from a table using Column and row names as inpu
Roger tried to start in A1, but lost a colon and had some trouble with ()s:
=INDEX($A$1:E$6,MATCH(Name,$A:$A,0),MATCH(week,$1: $1,0)) or =INDEX($A$1:E$6;MATCH("Company 5";A1:A6;0);MATCH("Week 45";A1:E1;0)) Olle wrote: Thank you very much for your quick response! I tried your suggested formula and with some minor adjustments it almost got it to work. Using the below formula I don't get the "Company 5, Week 45" value, I get the "Company 4, Week 44" instead. How can that be? Solution? =INDEX($A$1:E$6;MATCH("Company 5";A2:A6;0);MATCH("Week 45";B1:E1;0)) Regars Olle "Roger Govier" wrote: Hi Use the MATCH function as well as INDEX. =INDEX($A$1:E$6,MATCH(Name,$A$A,0),MATCH(week,($1: $1,0)) Substitute the word Name and Week with the cell references holding the Company and Week Number or insert their text as "Company2" and "Week 44" for example. -- Regards Roger Govier "Olle" wrote in message ... What cellformula, using column and row names, return a specific value from a table like below? (I cant use the INDEX function since I dont know the column or row numbers in the actual table, only the names) A B C D F 1 Week 42 Week 43 Week 44 Week 45 2 Company 1 67% 71% 69% 81% 3 Company 2 45% 78% 79% 73% 4 Company 3 88% 67% 86% 74% 5 Company 4 97% 56% 77% 92% 6 Company 5 87% 55% 82% 69% -- Dave Peterson |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return a value from a table using Column and row names as inpu
Dont mind my last question, Solved it, thx. Right formula becomes:
=INDEX($A$1:E$6;MATCH("Company 2";A1:A6;0);MATCH("Week 43";A1:E1;0)) Regards Olle "Olle" wrote: Thank you very much for your quick response! I tried your suggested formula and with some minor adjustments it almost got it to work. Using the below formula I don't get the "Company 5, Week 45" value, I get the "Company 4, Week 44" instead. How can that be? Solution? =INDEX($A$1:E$6;MATCH("Company 5";A2:A6;0);MATCH("Week 45";B1:E1;0)) Regars Olle "Roger Govier" wrote: Hi Use the MATCH function as well as INDEX. =INDEX($A$1:E$6,MATCH(Name,$A$A,0),MATCH(week,($1: $1,0)) Substitute the word Name and Week with the cell references holding the Company and Week Number or insert their text as "Company2" and "Week 44" for example. -- Regards Roger Govier "Olle" wrote in message ... What cellformula, using column and row names, return a specific value from a table like below? (I cant use the INDEX function since I dont know the column or row numbers in the actual table, only the names) A B C D F 1 Week 42 Week 43 Week 44 Week 45 2 Company 1 67% 71% 69% 81% 3 Company 2 45% 78% 79% 73% 4 Company 3 88% 67% 86% 74% 5 Company 4 97% 56% 77% 92% 6 Company 5 87% 55% 82% 69% |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return a value from a table using Column and row names as inpu
Hi
Glad you managed to work it out, and that Dave spotted my "fat fingered" typing again!!! -- Regards Roger Govier "Olle" wrote in message ... Dont mind my last question, Solved it, thx. Right formula becomes: =INDEX($A$1:E$6;MATCH("Company 2";A1:A6;0);MATCH("Week 43";A1:E1;0)) Regards Olle "Olle" wrote: Thank you very much for your quick response! I tried your suggested formula and with some minor adjustments it almost got it to work. Using the below formula I don't get the "Company 5, Week 45" value, I get the "Company 4, Week 44" instead. How can that be? Solution? =INDEX($A$1:E$6;MATCH("Company 5";A2:A6;0);MATCH("Week 45";B1:E1;0)) Regars Olle "Roger Govier" wrote: Hi Use the MATCH function as well as INDEX. =INDEX($A$1:E$6,MATCH(Name,$A$A,0),MATCH(week,($1: $1,0)) Substitute the word Name and Week with the cell references holding the Company and Week Number or insert their text as "Company2" and "Week 44" for example. -- Regards Roger Govier "Olle" wrote in message ... What cellformula, using column and row names, return a specific value from a table like below? (I cant use the INDEX function since I dont know the column or row numbers in the actual table, only the names) A B C D F 1 Week 42 Week 43 Week 44 Week 45 2 Company 1 67% 71% 69% 81% 3 Company 2 45% 78% 79% 73% 4 Company 3 88% 67% 86% 74% 5 Company 4 97% 56% 77% 92% 6 Company 5 87% 55% 82% 69% |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return a value from a table using Column and row names as inpu
Thanks Dave!
I managed to get it working using the example table with company y and week x. However, when I try in my actual table, which is a pivottable, it don't work. I get values but not the right ones. Would you know how to extract at value from a corresponding pivottable? Regards Olle "Dave Peterson" wrote: Roger tried to start in A1, but lost a colon and had some trouble with ()s: =INDEX($A$1:E$6,MATCH(Name,$A:$A,0),MATCH(week,$1: $1,0)) or =INDEX($A$1:E$6;MATCH("Company 5";A1:A6;0);MATCH("Week 45";A1:E1;0)) Olle wrote: Thank you very much for your quick response! I tried your suggested formula and with some minor adjustments it almost got it to work. Using the below formula I don't get the "Company 5, Week 45" value, I get the "Company 4, Week 44" instead. How can that be? Solution? =INDEX($A$1:E$6;MATCH("Company 5";A2:A6;0);MATCH("Week 45";B1:E1;0)) Regars Olle "Roger Govier" wrote: Hi Use the MATCH function as well as INDEX. =INDEX($A$1:E$6,MATCH(Name,$A$A,0),MATCH(week,($1: $1,0)) Substitute the word Name and Week with the cell references holding the Company and Week Number or insert their text as "Company2" and "Week 44" for example. -- Regards Roger Govier "Olle" wrote in message ... What cellformula, using column and row names, return a specific value from a table like below? (I cant use the INDEX function since I dont know the column or row numbers in the actual table, only the names) A B C D F 1 Week 42 Week 43 Week 44 Week 45 2 Company 1 67% 71% 69% 81% 3 Company 2 45% 78% 79% 73% 4 Company 3 88% 67% 86% 74% 5 Company 4 97% 56% 77% 92% 6 Company 5 87% 55% 82% 69% -- Dave Peterson |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Return a value from a table using Column and row names as inpu
Hi
If you are extracting the data from a Pivot Table, use the GetPivotData command which is analogous to using Index Match, but works specifically on PT's. Debra Dalgleish has some good help on how to use this at http://www.contextures.com/xlPivot06.html -- Regards Roger Govier "Olle" wrote in message ... Thanks Dave! I managed to get it working using the example table with company y and week x. However, when I try in my actual table, which is a pivottable, it don't work. I get values but not the right ones. Would you know how to extract at value from a corresponding pivottable? Regards Olle "Dave Peterson" wrote: Roger tried to start in A1, but lost a colon and had some trouble with ()s: =INDEX($A$1:E$6,MATCH(Name,$A:$A,0),MATCH(week,$1: $1,0)) or =INDEX($A$1:E$6;MATCH("Company 5";A1:A6;0);MATCH("Week 45";A1:E1;0)) Olle wrote: Thank you very much for your quick response! I tried your suggested formula and with some minor adjustments it almost got it to work. Using the below formula I don't get the "Company 5, Week 45" value, I get the "Company 4, Week 44" instead. How can that be? Solution? =INDEX($A$1:E$6;MATCH("Company 5";A2:A6;0);MATCH("Week 45";B1:E1;0)) Regars Olle "Roger Govier" wrote: Hi Use the MATCH function as well as INDEX. =INDEX($A$1:E$6,MATCH(Name,$A$A,0),MATCH(week,($1: $1,0)) Substitute the word Name and Week with the cell references holding the Company and Week Number or insert their text as "Company2" and "Week 44" for example. -- Regards Roger Govier "Olle" wrote in message ... What cellformula, using column and row names, return a specific value from a table like below? (I cant use the INDEX function since I dont know the column or row numbers in the actual table, only the names) A B C D F 1 Week 42 Week 43 Week 44 Week 45 2 Company 1 67% 71% 69% 81% 3 Company 2 45% 78% 79% 73% 4 Company 3 88% 67% 86% 74% 5 Company 4 97% 56% 77% 92% 6 Company 5 87% 55% 82% 69% -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|