Excel Formula Help - In Which Year did X Occur

[Sundance_Kid]

Hi,

See the below image, I want a formula to tell me in which year (most recent) was the last amount of Sales greater than 0. AS you can see for Product A that will have been 2021, but for Product B it will be 2024.

Is there some formula that can return the correct (most recent) year.

Thanks

 

[Phuoc]

Try this

Book1
	A	B	C	D	E	F	G	H	I
1
2		2019	2020	2021	2022	2023	2024
3		500	0	0	0	0	0		2019
4		500	500	500	0	0	0		2021
Sheet1

Cell Formulas
Range	Formula
I3:I4	I3	=LOOKUP(2,1/(B3:G3>0),$B$2:$G$2)

 

[SanjayGMusafir]

Used @Phuoc sample data to work on it.

@Sundance_Kid it better to share sample data than a picture

Check this and revert -

Book2
	A	B	C	D	E	F	G	H	I
1
2		2019	2020	2021	2022	2023	2024
3		500	0	0	0	0	0		2019
4		500	500	500	0	0	0		2021
5
Sheet1

Cell Formulas
Range	Formula
C2:G2	C2	=B2+1
I3	I3	=MAXIFS(B2:G2,B3:G3,">0")
I4	I4	=MAXIFS(B2:G2,B4:G4,">0")

 

[Alex Blakenburg]

If you are not worried about users being on older versions of Excel then you can use XLookup (looking up last to first)
In I3

=XLOOKUP(TRUE,$B3:$G3>0,$B$2:$G$2,,0,-1)

 

[Alex Blakenburg]

@SanjayGMusafir, interesting MAXIFS is not the first thing that came to my mind. A downside seems to be that if you convert the data set into an Excel Table, the year headings become text and the formula returns 0.

 

[Sundance_Kid]

Thanks all, sorted based on your replies above. Very helpful.

 

[Arts]

Hi may I ask how the TRUE works within the Lookup value of the XLOOKUP please?
=XLOOKUP(TRUE,$B3:$G3>0,$B$2:$G$2,,0,-1)[/CODE]

Ignore I think I have it, the TRUE value is the TRUE from the lookup_array>0 ?

 

[SanjayGMusafir]

Glad to help you and thanks for the feedback.

 

[Alex Blakenburg]

Ignore I think I have it, the TRUE value is the TRUE from the lookup_array>0 ?

You are correct, if you highlight the lookup array (2nd parameter) and hit F9 you will see this:

=XLOOKUP(TRUE, {TRUE,TRUE,TRUE,FALSE,FALSE,FALSE}, $B$2:$G$2, , 0, -1)

the last parameter "-1" tells XLooup to look from right to left (last occurence)

Note: When you use more than one criteria they would be joined by a multiplication operator "*" (AND logic), this converts TRUEs to 1s and FALSE to 0s. If you do this your lookup value needs to be changed from being TRUE to 1.
eg you can force this with one criteria by adding "1*" or a double unary "--"

=XLOOKUP(1, 1*($B3:$G3>0), $B$2:$G$2,,0,-1)

F9'd

=XLOOKUP(1, {1,1,1,0,0,0}, $B$2:$G$2,,0,-1)

This exceljet link may help.

XLOOKUP with multiple criteria

The best way to use XLOOKUP with multiple criteria is to use Boolean logic to apply conditions. In the example shown, the formula in H8 is: =XLOOKUP(1,(B5:B15=H5)*(C5:C15=H6)*(D5:D15=H7),E5:E15) XLOOKUP returns $29.00, the price for a Medium Blue Hoodie. Note the lookup_value in XLOOKUP is 1...

exceljet.net

 

[Arts]

Hi Alex thank you for the very helpful/ detailed response, very much appreciated!

Through out all these years using excel I had always thought the lookup_value needed to be sourced from a location as such, found it quite smart how you came to that within the formula itself (probably the norm to most excel users on here but a great insight for me)

I'll be sure to go over the link you sent as well! Thanks once again.