Jun 14 2021 11:55 AM
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.
Jun 14 2021 01:03 PM
SolutionAs 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)))
Jun 14 2021 02:01 PM
Jun 15 2021 12:11 AM
Jun 14 2021 01:03 PM
SolutionAs 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)))