Excel: look up the last column number within the different ranges and look up the figures in the column

[agasi]

		Jan	Feb	Mar	Apri	answer	answer
Number		red	blue	red	blue	red	blue	red	blue	red	blue
1	name	2	0		3					0	3
2	name					0	100			0	100
3	name	25	0
3	name							35	20	35	20
3	name			52	0
3	name			22	33

<tbody>
</tbody><colgroup><col span="10"><col><col></colgroup>

 

[agasi]

I don't know how to paste the table as image. Anyway, the formula I have is not looking up the correct value. I need to find the last column within the same Number range and return the values. It is not looking up the last Value PER Row. For example, there are 4 rows under "3 name" and the last column within 3 name is April. So each 3 name row should return the values in April. At the moment, first,third and fourth are blank so it should be blank except second 3 name.

 

[63falcondude]

I'm not sure how to only do it once per number, but here is an example of the formula for line by line:

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.

 

[63falcondude]

I can do it with helper columns but it's ugly. Real ugly.

 

[63falcondude]

ADVERTISEMENT

Hopefully someone has a better solution than this:

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.

 

[deciog]

63falconude


Thanks for the solutions with explanation of the formula this is a very valuable learning, I am learning and understanding the operation of them.


I just have to thank and congratulate your explanations.


Sorry if English is not correct because I am using Google Translate, my native language is Portuguese of Brazil.


Hugs
Décio Gassi

 

[agasi]

ADVERTISEMENT

63falcondude, thanks. The formula I had done the exactly same job as you done. But unfortunately, it is not the one I am after.

Sorry, I didn't notice that you have posted the second reply. Yes, that is the one. Thanks. I will follow the steps you have described. Thanks again.

 

[agasi]

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.

 

[63falcondude]

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.

That is correct. I am glad to help.

 

[agasi]

63falcondude, another problem. throughout the months some values repeat. So to find the column number using Match is not looking up the last column number if the same values are repeated. Match looks up the first orruences. So I will have to amend the Match (perhaps using Lookup and using Column). I will try to amend and post it back later.