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:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Jan</td><td style="text-align: center;;"></td><td style="text-align: center;;">Feb</td><td style="text-align: center;;"></td><td style="text-align: center;;">Mar</td><td style="text-align: center;;"></td><td style="text-align: center;;">Apr</td><td style="text-align: center;;"></td><td style="text-align: center;;">answer</td><td style="text-align: center;;">answer</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Number</td><td style="text-align: center;;"></td><td style="text-align: center;;">red</td><td style="text-align: center;;">blue</td><td style="text-align: center;;">red</td><td style="text-align: center;;">blue</td><td style="text-align: center;;">red</td><td style="text-align: center;;">blue</td><td style="text-align: center;;">red</td><td style="text-align: center;;">blue</td><td style="text-align: center;;">red</td><td style="text-align: center;;">blue</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">name</td><td style="text-align: center;;">2</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">2</td><td style="text-align: center;;">name</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">100</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">name</td><td style="text-align: center;;">25</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">25</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">3</td><td style="text-align: center;;">name</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">35</td><td style="text-align: center;;">20</td><td style="text-align: center;;">35</td><td style="text-align: center;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">3</td><td style="text-align: center;;">name</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">52</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">52</td><td style="text-align: center;;">0</td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">3</td><td style="text-align: center;;">name</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">22</td><td style="text-align: center;;">33</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">22</td><td style="text-align: center;;">33</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K3</th><td style="text-align:left">{=LOOKUP(<font color="Blue">9.99E+307,IF(<font color="Red">(<font color="Green">$C3:$J3<>""</font>)*(<font color="Green">$C$2:$J$2=K$2</font>),$C3:$J3</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[63falcondude]

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

 

[63falcondude]

ADVERTISEMENT

Hopefully someone has a better solution than this:

<b>Excel 2010</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #BBB"><colgroup><col width="25px" style="background-color: #DAE7F5" /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #DAE7F5;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th><th>J</th><th>K</th><th>L</th><th>M</th><th>N</th><th>O</th><th>P</th><th>Q</th><th>R</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Jan</td><td style="text-align: center;;"></td><td style="text-align: center;;">Feb</td><td style="text-align: center;;"></td><td style="text-align: center;;">Mar</td><td style="text-align: center;;"></td><td style="text-align: center;;">Apr</td><td style="text-align: center;;"></td><td style="text-align: center;;">answer</td><td style="text-align: center;;">answer</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style=";">final answer</td><td style=";">final answer</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">Number</td><td style="text-align: center;;"></td><td style="text-align: center;;">red</td><td style="text-align: center;;">blue</td><td style="text-align: center;;">red</td><td style="text-align: center;;">blue</td><td style="text-align: center;;">red</td><td style="text-align: center;;">blue</td><td style="text-align: center;;">red</td><td style="text-align: center;;">blue</td><td style="text-align: center;;">red</td><td style="text-align: center;;">blue</td><td style="text-align: center;;">MATCH red</td><td style="text-align: center;;">MATCH blue</td><td style="text-align: center;;">MAX red</td><td style="text-align: center;;">MAX blue</td><td style="text-align: center;;">red</td><td style="text-align: center;;">blue</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">name</td><td style="text-align: center;;">2</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td><td style="text-align: center;;">1</td><td style="text-align: center;;">4</td><td style="text-align: center;;">2</td><td style="text-align: center;;">3</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">2</td><td style="text-align: center;;">name</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">100</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">0</td><td style="text-align: center;;">100</td><td style="text-align: center;;">5</td><td style="text-align: center;;">6</td><td style="text-align: center;;">5</td><td style="text-align: center;;">6</td><td style="text-align: center;;">0</td><td style="text-align: center;;">100</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">3</td><td style="text-align: center;;">name</td><td style="text-align: center;;">25</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">25</td><td style="text-align: center;;">0</td><td style="text-align: center;;">1</td><td style="text-align: center;;">2</td><td style="text-align: center;;">7</td><td style="text-align: center;;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">3</td><td style="text-align: center;;">name</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">35</td><td style="text-align: center;;">20</td><td style="text-align: center;;">35</td><td style="text-align: center;;">20</td><td style="text-align: center;;">7</td><td style="text-align: center;;">8</td><td style="text-align: center;;">7</td><td style="text-align: center;;">8</td><td style="text-align: center;;">35</td><td style="text-align: center;;">20</td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">3</td><td style="text-align: center;;">name</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">52</td><td style="text-align: center;;">0</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">52</td><td style="text-align: center;;">0</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">7</td><td style="text-align: center;;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">3</td><td style="text-align: center;;">name</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">22</td><td style="text-align: center;;">33</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">22</td><td style="text-align: center;;">33</td><td style="text-align: center;;">3</td><td style="text-align: center;;">4</td><td style="text-align: center;;">7</td><td style="text-align: center;;">8</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #BBB;border-top:none;text-align: center;background-color: #DAE7F5;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">M3</th><td style="text-align:left">=MATCH(<font color="Blue">K3,$C3:$J3,0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">N3</th><td style="text-align:left">=MATCH(<font color="Blue">L3,$C3:$J3,0</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">Q3</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">$M3=$O3,$N3=$P3</font>),K3,""</font>)</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">R3</th><td style="text-align:left">=IF(<font color="Blue">AND(<font color="Red">$M3=$O3,$N3=$P3</font>),L3,""</font>)</td></tr></tbody></table></td></tr></table><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">K3</th><td style="text-align:left">{=LOOKUP(<font color="Blue">9.99E+307,IF(<font color="Red">(<font color="Green">$C3:$J3<>""</font>)*(<font color="Green">$C$2:$J$2=K$2</font>),$C3:$J3</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">L3</th><td style="text-align:left">{=LOOKUP(<font color="Blue">9.99E+307,IF(<font color="Red">(<font color="Green">$C3:$J3<>""</font>)*(<font color="Green">$C$2:$J$2=L$2</font>),$C3:$J3</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">O3</th><td style="text-align:left">{=MAX(<font color="Blue">IF(<font color="Red">$A$3:$A$8=$A3,M$3:M$8,0</font>)</font>)}</td></tr><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">P3</th><td style="text-align:left">{=MAX(<font color="Blue">IF(<font color="Red">$A$3:$A$8=$A3,N$3:N$8,0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[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.