Formula need to pick the adjacent header of last value greater than Zero

N

Negi1984

Board Regular
Joined
May 6, 2011
Messages
199
Hi All,

I have some data from column R to column CZ. Row 1 contains the Headers.
Now I need to look for last non-zero value and pick the header next that cell.

I am using the below formula right now but its not giving me the next header.
its giving me the header of cell where last non zero value exist.

=LOOKUP(2,1/(R2:CW2<>0),$R$1:$CW$1)
Please suggest, what changes I can do in the above formula. or any other formula to solve the issue.

Regards,
Rajender
 
Sort by date Sort by votes
Hi Aladin,

Thanks a ton for helping me out to solve the issue.
its working fine.

Have a nice day to you.

Aladin Akyurek said:
ABCDEFGHIJKLM
Outputintermediate resultResult
100001####
11##110000
1000010###
11110101##

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"]Jan-14[/TD]
[TD="align: right"]Feb-14[/TD]
[TD="align: right"]Mar-14[/TD]
[TD="align: right"]Apr-14[/TD]
[TD="align: right"]May-14[/TD]
[TD="align: right"]Jun-14[/TD]
[TD="align: right"]Jul-14[/TD]
[TD="align: right"]Aug-14[/TD]
[TD="align: right"]Sep-14[/TD]
[TD="align: right"]Oct-14[/TD]

[TD="align: center"]2[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0.25[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]Feb-14[/TD]

[TD="align: right"]Feb-14[/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]1111111111[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]Jul-14[/TD]

[TD="align: right"]Jul-14[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]1111111111[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]Aug-14[/TD]
[TD="align: right"]1111101000[/TD]
[TD="align: right"]Aug-14[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]Jun-14[/TD]
[TD="align: right"]1111100000[/TD]
[TD="align: right"]Jun-14[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]0000000000[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]Jul-14[/TD]
[TD="align: right"]1111110000[/TD]
[TD="align: right"]Jul-14[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]#N/A[/TD]
[TD="align: right"]1111111111[/TD]
[TD="align: right"]#N/A[/TD]

[TD="align: center"]11[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: right"]0.25[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=B4C6E7]#B4C6E7[/URL] , align: right"][/TD]
[TD="align: right"]Jul-14[/TD]

[TD="align: right"]Jul-14[/TD]

[TD="align: center"]12[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]0[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"]1[/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"][/TD]
[TD="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=D9E1F2]#D9E1F2[/URL] , align: right"][/TD]
[TD="align: right"]Jul-14[/TD]

[TD="align: right"]Jul-14[/TD]

</tbody>
Sheet1​



Install first the following udf Sreverse as module by means of Alt+F11...

Public Function Sreverse(Rng As Range)
Sreverse = StrReverse(Rng.Text)
End Function

In L2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN("",TRUE,IF(ISNUMBER(A2:J2),IF(ABS(A2:J2)>0,1,0),"#"))

In M2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$1:$J$1,COLUMNS($A2:$J2)-LOOKUP(9.99999999999999E+307,FIND("01",Sreverse(L2)))+1),#N/A)

Note. If Sreverse can be modified to admit an array refernce, we can eliminate the intermediate result.
Click to expand...
 
Upvote 0

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
You must log in or register to reply here.

Similar threads

H
  • Question Question
Value of 1 column before the last value in a contiguous range
Replies
3
Views
218
Kaper
Kaper
J
  • Solved
Value in column adjacent to the value found by LOOKUP
Replies
5
Views
138
Fluff
Fluff
N
  • Question Question
Formula help to search a table for a date greater than today that has a non-blank cell and return the date in a separate cell.
Replies
2
Views
506
Alex Blakenburg
A
T
  • Solved
Index and Match (?) for the last non-zero value in a row
Replies
3
Views
683
Fluff
Fluff
M
  • Solved
run formula based on cell value to the last row
Replies
5
Views
381
MMM_84
M

Forum statistics

Threads
1,223,911
Messages
6,175,331
Members
452,636
Latest member
laura12345

Share this page

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
Back
Top