How do I get first n non-zero values in a row?

user_9646230 0
Viewed 658

I currently have this formula but it returns the non-zero values in the opposite order I want.

Here's the formula:

=INDEX($R275:$BE275,LARGE(IF($R275:$BE275<>0,COLUMN($R275:$BE275)- MIN(COLUMN($R275:$BE275))+1),COLUMNS($A:A)))

This returns the first non-zero value from column BE looking back towards R, when I want it to work the other way, the first instance from right to left. When I draft the formula to the right, I want it to pick up the next non-zero value, right to left.

If I could get this figured out, as well as how to return the column header as well, that would be great.

excel-formula excel
1 Answers

Screenshot/here refer.

The formula you provided returns the last non-zero value which is also what you state you're trying to find:

"I want...the first instance from right to left."

To avoid ambiguity, I provide solutions for the first such occurrence (and corresponding heading), and a revised method to determine the last occurrence (or first occurrence "from right to left").

First occurrence (working from left to right)

Function can be simplified significantly to the following:

=INDEX(A2:H2,0,MATCH(1,--(A2:H2<>0),0))

First occurrence - working from left to right

Corresponding heading:

=INDEX($A$1:$H$1,0,MATCH(1,--(A2:H2<>0),0))

Per @ScottCraner, this also works (albeit uses the same unnecessarily long function):

=INDEX($R275:$BE275,SMALL(IF($R275:$BE275<>0,COLUMN($R275:$BE275)- MIN(COLUMN($R275:$BE275))+1),COLUMNS($A:A)))

Last occurrence

Revised function to consider (requires Office 365 compatible version of Excel, and customisation to return corresponding heading)..

=LET(reverse,INDEX(A2:H2,0,SEQUENCE(1,COUNTA(A2:H2),COUNTA(A2:H2),-1)),INDEX(reverse,MATCH(1,--(reverse<>0),0)))

First occurrence - working from right to left

user_136076720
Related