SOLVED

Find latest value by searching right to left - formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2445863%22%20slang%3D%22en-US%22%3EFind%20latest%20value%20by%20searching%20right%20to%20left%20-%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2445863%22%20slang%3D%22en-US%22%3E%3CP%3EDon%E2%80%99t%20suppose%20anyone%20can%20help%20with%20a%20formula%20please%3F%20I%20am%20trying%20to%20find%20a%20%22first%22%20and%20%22last%22%20sale%20date.%20I%E2%80%99ve%20achieved%20the%20first%20sale%20date%20by%20using%20this%20formula%20below%2C%20now%20I%20essentially%20want%20it%20to%20do%20the%20exact%20same%20thing%20backwards%20(searching%20right%20to%20left)%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%7B%3DMATCH(TRUE%2CINDEX(%24B19%3A%24IV19%26gt%3B0%2C0)%2C0)*(ISNUMBER(B19%3AIV19))%7D%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20then%20created%20a%20formula%20which%20matches%20the%20column%20number%20(Row%201)%20with%20the%20corresponding%20date%20in%20Row%204.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EMy%20data%20runs%20horizontally%20and%20is%20spread%20out%20over%20the%20course%20of%20months%2C%20so%20I%20need%20it%20to%20search%20from%20right%20to%20left%20(Row%20IV5%20to%20B5)%20and%20return%20the%20position%20(column%20number)%20of%20the%20first%20cell%20which%20is%20greater%20than%20zero%20so%20I%20can%20see%20when%20the%20last%20order%20was%20placed%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22AllyLavin_0-1623696669702.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F288521i6CD69CFD15A84A1D%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22AllyLavin_0-1623696669702.png%22%20alt%3D%22AllyLavin_0-1623696669702.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20sales%20data%20comes%20from%20an%20INDEX%20%26amp%3B%20MATCH%20formula%2C%20so%20although%20some%20cells%20are%20%E2%80%98blank%E2%80%99%20they%20still%20essentially%20hold%20zero%20values%20so%20I%20need%20the%20formula%20to%20ignore%20them%20and%20only%20return%20a%20result%20when%20it%20finds%20a%20value%20greater%20than%20zero.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20tried%20this%20formula%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DLOOKUP(99999999%2CIF(A5%3AIV5%26lt%3B%26gt%3B0%2CA5%3AIV5))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Eand%20it%20gives%20me%20the%20correct%20value%20for%20the%20cell%20I%E2%80%99m%20looking%20for%2C%20but%20I%20need%20the%20column%20position%2C%20not%20the%20cell%20value%20%E2%80%93%20that%20way%20I%20can%20match%20the%20column%20number%20to%20the%20date%20range%2C%20so%20it%20tells%20me%20last%20ordered%20in%20September%202016%20for%20example.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%E2%80%99m%20a%20self-taught%20excel%20novice%20so%20please%20excuse%20my%20attempt%20at%20making%20sense%20of%20this!%20Any%20suggestions%20would%20be%20appreciated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2445863%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2446089%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20latest%20value%20by%20searching%20right%20to%20left%20-%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2446089%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1078631%22%20target%3D%22_blank%22%3E%40AllyLavin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20an%20array%20formula%20confirmed%20with%20Ctrl%2BShift%2BEnter%3A%3C%2FP%3E%0A%3CP%3E%3DINDEX(%24B%244%3A%24IV%244%2CMAX((%24B5%3A%24IV5%26gt%3B0)*ISNUMBER(%24B5%3A%24IV5)*(COLUMN(%24B%244%3A%24IV%244)-COLUMN(%24B%244)%2B1)))%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2446342%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20latest%20value%20by%20searching%20right%20to%20left%20-%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2446342%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1078631%22%20target%3D%22_blank%22%3E%40AllyLavin%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20variant%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DXLOOKUP(1%2C--(sales%26gt%3B0)%2Cdates%2C%2C%2C-1)%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2447164%22%20slang%3D%22en-US%22%3ERe%3A%20Find%20latest%20value%20by%20searching%20right%20to%20left%20-%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2447164%22%20slang%3D%22en-US%22%3EThis%20is%20perfect!%20thank%20you%20so%20much%3C%2FLINGO-BODY%3E
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