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

agasi

New Member
Joined
Nov 22, 2016
Messages
48
JanFebMarApriansweranswer
Numberredblueredblueredblueredblueredblue
1name20 3 03
2name 0100 0100
3name250
3name 35203520
3name 520
3name 2233

<tbody>
</tbody><colgroup><col span="10"><col><col></colgroup>
 
Last edited:

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

agasi

New Member
Joined
Nov 22, 2016
Messages
48
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

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
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

Well-known Member
Joined
Jan 15, 2016
Messages
3,572

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

Board Regular
Joined
Mar 26, 2016
Messages
59
Office Version
  1. 365
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

New Member
Joined
Nov 22, 2016
Messages
48

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.
 
Last edited:

agasi

New Member
Joined
Nov 22, 2016
Messages
48
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

Well-known Member
Joined
Jan 15, 2016
Messages
3,572
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

New Member
Joined
Nov 22, 2016
Messages
48
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,481
Messages
5,601,918
Members
414,482
Latest member
morkar

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top