Excel 2010 | ||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | |||
1 | Jan | Feb | Mar | Apr | answer | answer | ||||||||
2 | Number | red | blue | red | blue | red | blue | red | blue | red | blue | |||
3 | 1 | name | 2 | 0 | 3 | 2 | 3 | |||||||
4 | 2 | name | 0 | 100 | 0 | 100 | ||||||||
5 | 3 | name | 25 | 0 | 25 | 0 | ||||||||
6 | 3 | name | 35 | 20 | 35 | 20 | ||||||||
7 | 3 | name | 52 | 0 | 52 | 0 | ||||||||
8 | 3 | name | 22 | 33 | 22 | 33 | ||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
K3 | {=LOOKUP(9.99E+307,IF(($C3:$J3<>"")*($C$2:$J$2=K$2),$C3:$J3))} | |
Press CTRL+SHIFT+ENTER to enter array formulas. |
Excel 2010 | ||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | |||
1 | Jan | Feb | Mar | Apr | answer | answer | final answer | final answer | ||||||||||||
2 | Number | red | blue | red | blue | red | blue | red | blue | red | blue | MATCH red | MATCH blue | MAX red | MAX blue | red | blue | |||
3 | 1 | name | 2 | 0 | 3 | 2 | 3 | 1 | 4 | 1 | 4 | 2 | 3 | |||||||
4 | 2 | name | 0 | 100 | 0 | 100 | 5 | 6 | 5 | 6 | 0 | 100 | ||||||||
5 | 3 | name | 25 | 0 | 25 | 0 | 1 | 2 | 7 | 8 | ||||||||||
6 | 3 | name | 35 | 20 | 35 | 20 | 7 | 8 | 7 | 8 | 35 | 20 | ||||||||
7 | 3 | name | 52 | 0 | 52 | 0 | 3 | 4 | 7 | 8 | ||||||||||
8 | 3 | name | 22 | 33 | 22 | 33 | 3 | 4 | 7 | 8 | ||||||||||
Sheet1 |
Cell Formulas | ||
---|---|---|
Range | Formula | |
M3 | =MATCH(K3,$C3:$J3,0) | |
N3 | =MATCH(L3,$C3:$J3,0) | |
Q3 | =IF(AND($M3=$O3,$N3=$P3),K3,"") | |
R3 | =IF(AND($M3=$O3,$N3=$P3),L3,"") | |
K3 | {=LOOKUP(9.99E+307,IF(($C3:$J3<>"")*($C$2:$J$2=K$2),$C3:$J3))} | |
L3 | {=LOOKUP(9.99E+307,IF(($C3:$J3<>"")*($C$2:$J$2=L$2),$C3:$J3))} | |
O3 | {=MAX(IF($A$3:$A$8=$A3,M$3:M$8,0))} | |
P3 | {=MAX(IF($A$3:$A$8=$A3,N$3:N$8,0))} | |
Press CTRL+SHIFT+ENTER to enter array formulas. |
63falcondude, I see. So find the values first per line and find the max column number within the same range in Column A and use if to return the values. That is very cleaver. Thanks a lot.