SOLVED

New Contributor

# Find latest value by searching right to left - formula

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):

{=MATCH(TRUE,INDEX(\$B19:\$IV19>0,0),0)*(ISNUMBER(B19:IV19))}

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;

=LOOKUP(99999999,IF(A5:IV5<>0,A5:IV5))

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)
Solution

# Re: Find latest value by searching right to left - formula

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

=INDEX(\$B\$4:\$IV\$4,MAX((\$B5:\$IV5>0)*ISNUMBER(\$B5:\$IV5)*(COLUMN(\$B\$4:\$IV\$4)-COLUMN(\$B\$4)+1)))

# Re: Find latest value by searching right to left - formula

As variant

``=XLOOKUP(1,--(sales>0),dates,,,-1)``

# Re: Find latest value by searching right to left - formula

This is perfect! thank you so much