Excel Formula To Get LAST Non-Zero Value in Row and Return Column Header

I currently have a spreadsheet of transactions, and I want to write a formula that will help me extract from each row a customer's most recent transaction.

Additionally, I want to then pull the column header associated with each most recent transaction, as to isolate the date on which the transaction occurred.

I have found the formula:

=INDEX($E$1:$CO$1, MATCH(TRUE,INDEX(E2:CO2<>0,),0)) 

This is giving me the most recent transaction; it is searching each row for the first non-zero instance.

I need the LAST non zero instance. Or alternatively, I need to know how to make this formula work from right to left, instead of left to right.

Any help is much appreciated!

Solution 1:

No need to alter your data, you can use a LOOKUP function to find the last non-zero and get the header like this

=LOOKUP(2,1/(E2:CO2<>0),E$1:CO$1)

This works because the 1/(E2:CO2<>0) part returns an array of either 1s where the cell isn't zero......or #DIV/0! errors where it is. LOOKUP then won't find 2 in that array so it matches with the last 1, i.e. the last column with a non-zero value.....and it then returns the corresponding value from the header row.

If you actually want the last number in a row (including zero) this is even simpler, just

=LOOKUP(9.99E+307,E2:CO2,E$1:CO$1)

Solution 2:

Based off here and here, the quick answer is you likely need to reverse you data. Excel doesn't have any functions that move right to left.

Assuming the data you wanted to reverse extended from A2 to E2:

=INDEX($A$2:$E$2,6-COLUMNS($A$2:A2))

Where 6 is your number of columns (5) + 1. If you prefer not to use a hardcoded value (6), as pointed out in the comments from the second link, you can use something like COUNTA($A$2:$E$2)+1. The formula would thus look like:

=INDEX($A$2:$E$2,COUNTA($A$2:$E$2)+1-COLUMNS($A2:A2))

Note that the formula hinges on the final value of COLUMNS (A2 to start) to derive the correct cell value. Assuming you copy and paste, you would end up with a row of cells as follows (assuming you wanted to display all 5 pieces of data):

=INDEX($A$2:$E$2,6-COLUMNS($A$2:A2))
[...]
=INDEX($A$2:$E$2,6-COLUMNS(A$2:$E2))

An example of what this looks like:

Including the formula to find the first non-zero value: