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

 

[Alex Blakenburg]

@Arts prior to Dynamic Array capability (MS 365 / O 2021 or greater), that concept would only have worked using Index Match entered as an array formula using "Control + Shift + Enter". The closest you would have gotten to that method would be the Lookup formula using the format "=Lookup(2, 1/((range1=value1)*(range2=value2)), range_to_return)", so you weren't wrong in your thinking.

 

[Arts]

@Alex Blakenburg I think the closest I would have come to using any method would have been a post on this forum asking for help!

But what you just mentioned about using older versions I don't have excel 365 at home (client where I work does) so I often try to resolve problems on this board on older versions of excel for learning purposes. Had a bit of down time now so was actually playing around with this current post seeing how I would have solved it without XLOOKUP (you mentioned the lookup method so will look into that as well) below image of me messing around to see how I would have gone about it....(sorry for the digression)

But this board is a great place to learn and even more helpful when guys like you Rory, joe, Peter, Fluff and co take the time out to only solve issues but also explain.

 

[Alex Blakenburg]

@Arts, there is the additional issue that you want the "last" occurence, the pre-dynamic array versions will find the first occurence.
I forgot that in post #2 @Phuoc, used Lookup if you want to try it out. It actually returns the last occurence and works in 2016.
PS: I am going to leave it there.

 

[Arts]

Hi

quick ques on this if @Phuoc could answer or anyone else in your LOOKUP formula your Lookup_value has 2 in this but when I enter a different number this still seems to return the desired result, could you please explain how this works ? The screenshot below I have changed the lookup_value to 5 I still seem to get the correct result in this instance

=LOOKUP(2,1/(B3:G3>0),$B$2:$G$2)

 

[Arts]

Posting for learning purposes (for others that may have also asked the question in previous post)

 

[Arts]

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)

Hi all

Sorry to re-visit an old (ish) post but in the quest of knowledge the question was very relevant to this example. The solution above had the formula

=LOOKUP(2,1/(B3:G3>0),$B$2:$G$2) which gave the desired result. What I was wondering was the 1/(B3:G3>0) aspect returns a 1 if a TRUE is given. (please confirm)

What I wanted to ask was that I had read that -- (double dash) also does this but if I was to replace the formula with

=LOOKUP(2,--(B3:G3>0),$B$2:$G$2) this gives me a different result.

In short why does -- and 1/ give different results if they are both mean't to converts TRUE to 1.

Many thanks as always

 

[Fluff]

if they are both mean't to converts TRUE to 1.

They don't do that, the double unary (--) will convert true to 1 & false to 0, but the 1/ will return a #div/0! error rather than 0 for false.

 

[Arts]

Noted! Thanks Fluff !

 

[Fluff]

You're welcome.

 

[Arts]

I can only apologising to keep coming back to this but where I understand one aspect another question pops up but hopefully this will be the last on it as this will answer why the outcome is different.

If I was to go down the route of -- (double dash) this would give me a bunch 1 and 0's to be precise =LOOKUP(2,--(B3:G3>0),$B$2:$G$2) highlighted aspect gives {1,0,0,0,0,0) image attached.

In theory this should work?? As we are looking for >0 (B3:G3>0) so why would this return a different result to the original formula =LOOKUP(2,1/(B3:G3>0),$B$2:$G$2) which is also returning a value of more than 0 (both methods have a 1 in the result and we are after more than 0 but seem to get different results)

so the lookup would look for the next largest value smaller than the lookup value of 2...

I hope what I am asking makes sense