Find latest value by searching right to left - formula

New Contributor

Don’t suppose anyone can help with a formula please? I am trying to find a "first" and "last" sale date. I’ve achieved the first sale date by using this formula below, now I essentially want it to do the exact same thing backwards (searching right to left):




I then created a formula which matches the column number (Row 1) with the corresponding date in Row 4.


My data runs horizontally and is spread out over the course of months, so I need it to search from right to left (Row IV5 to B5) and return the position (column number) of the first cell which is greater than zero so I can see when the last order was placed;





The sales data comes from an INDEX & MATCH formula, so although some cells are ‘blank’ they still essentially hold zero values so I need the formula to ignore them and only return a result when it finds a value greater than zero.


I tried this formula;




and it gives me the correct value for the cell I’m looking for, but I need the column position, not the cell value – that way I can match the column number to the date range, so it tells me last ordered in September 2016 for example.


I’m a self-taught excel novice so please excuse my attempt at making sense of this! Any suggestions would be appreciated.

3 Replies
best response confirmed by AllyLavin (New Contributor)


As an array formula confirmed with Ctrl+Shift+Enter:




As variant

This is perfect! thank you so much