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

microsoft excelworksheet-function

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!

Best Answer

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)

Related Question