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

#### agasi

##### New Member
 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>

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
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
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
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
I can do it with helper columns but it's ugly. Real ugly.

#### 63falcondude

##### Well-known Member

Hopefully someone has a better solution than this:

Excel 2010
ABCDEFGHIJKLMNOPQR
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
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

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

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.

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.

### Which adblocker are you using?

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

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