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:

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

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:


Excel 2010
ABCDEFGHIJKL
1JanFebMarApransweranswer
2Numberredblueredblueredblueredblueredblue
31name20323
42name01000100
53name250250
63name35203520
73name520520
83name22332233
Sheet1
Cell Formulas
RangeFormula
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

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

ADVERTISEMENT

Hopefully someone has a better solution than this:


Excel 2010
ABCDEFGHIJKLMNOPQR
1JanFebMarApransweranswerfinal answerfinal answer
2NumberredblueredblueredblueredblueredblueMATCH redMATCH blueMAX redMAX blueredblue
31name20323141423
42name0100010056560100
53name2502501278
63name3520352078783520
73name5205203478
83name223322333478
Sheet1
Cell Formulas
RangeFormula
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

Board Regular
Joined
Mar 26, 2016
Messages
62
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.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,675
Messages
5,838,716
Members
430,566
Latest member
ChanchalSingh

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