SOLVED

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

 

{=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;

 

AllyLavin_0-1623696669702.png

 

 

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

@AllyLavin 

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

 

@AllyLavin 

As variant

=XLOOKUP(1,--(sales>0),dates,,,-1)
This is perfect! thank you so much