Finding a cell value that is a few columns away on the same row, based on most recent value.

[Excelwhatsthat]

Hi folks,

I've got a grid of data as per the image (simplified just for context). I have an Xlookup that allows me to find the latest figure in 24/25, in this case value '2,692' (D6). This ensures the most recent week is showing in a dashboard. For context the formula that I'm using (which won't match the image is this: =XLOOKUP(2,1/(E13:E32<>0),E13:E32,"N/A",-1,-1)

The question I have is, how do I also extract the week # that matches what is pulled from the xlookup above? (i.e. value '5' - A6). I'm thinking some formula that identifies the latest figure, then reads across from that cell in the same row to the cell in column A. I'm sorry, but my Excel skills are middling at best. If someone can provide the solution with the logic behind it that would be amazing!

 

[Fluff]

How about

=XLOOKUP(2,1/(E13:E32<>0),A13:A32,"N/A",-1,-1)

 

[Excelwhatsthat]

How about

=XLOOKUP(2,1/(E13:E32<>0),A13:A32,"N/A",-1,-1)

Omg Fluff, you are a genius! It works perfectly! Thanks so much! Saved me literally hours trying to figure this out! Have a great day.

 

[Fluff]

You're welcome & thanks for the feedback.