Find last value bigger than zero based on criteria and return the date

[Yulyo]

Dear all,

I have a short question regarding a formula and I really need your help:
So I have a big Excel table, with about 200 columns and around 300000-400000 lines.

I have a small Dropdown on Sheet2!A2 (Image 2) containing the header in Sheet1 (image 1) and on Sheet2!C2 (Image 2) I need a formula that search for the value in Sheet2!A2, on Headers in Sheet1, search for the last value bigger than zero on that column, and return the date from column B (image 1).

Image 1

Image 2

I hope I made myself understood
Thank you.

 

[Legacy 143009]

So, in this case should it return 26.01.2022 (row 27)?

 

[Yulyo]

Yes, that's correct.
Sorry, I forgot to mention

 

[Legacy 143009]

Try this. I haven't checked it. I can help if it doesn't work.

=INDEX('Sheet1'!$B$1: $B$400000,MATCH(TRUE,INDEX('Sheet1'!$A$1:$GR$400000>0,0,MATCH($A$2,'Sheet1'!$A$1:$GR$1,0)),1))

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Legacy 143009]

Here is a working formula:

=INDEX('Sheet1'!$B$1:$B$400000,MAX(IF(INDEX('Sheet1'!$A$1:$GR$400000,0,MATCH($A$2,'Sheet1'!$A$1:$GR$1,0))<>0,ROW('Sheet1'!$A$1:$A$400000))))

 

[Legacy 143009]

Here is a working formula:

=INDEX('Sheet1'!$B$1:$B$400000,MAX(IF(INDEX('Sheet1'!$A$1:$GR$400000,0,MATCH($A$2,'Sheet1'!$A$1:$GR$1,0))<>0,ROW('Sheet1'!$A$1:$A$400000))))

By the way, this is an array formula. Just hitting Enter is not enough.
You should paste and press Ctrl+Shift+Enter together.

 

[Fluff]

That may not be needed, depending on what version of Excel the OP is using.

 

[Yulyo]

@Flashbond: everything is working just fine. Thank you so much for your help and sorry for the late answer
@Fluff: I will update my account details. Thank you.

 

[Legacy 143009]

@Flashbond: everything is working just fine. Thank you so much for your help and sorry for the late answer
@Fluff: I will update my account details. Thank you.

Gald to hear that it did work Thanks for the feedback