Matching a value (name) and finding the last non zero value in that row and return the value of corresponding column header value (a date)

[MazH]

Hi guys!

In my first sheet i have the list of employee names, and i would like a formula to lookup that name in the table below on sheet 2, look at that row, and return the date of the last non zero entry.

This would mean that Sam Smith returns 22 Apr 2024, Amy Andrews returns 19 Feb 2024 and Carl Crown returns 08 Apr 2024.

Help is much appreciated! I've used lookup and index formulas but it seems the sticky part is first matching the name, to know which row to look at.

TIA!

Row Labels	03 Jan 2024	22 Jan 2024	05 Feb 2024	19 Feb 2024	04 Mar 2024	18 Mar 2024	08 Apr 2024	22 Apr 2024
Sam Smith		1		1	1			1
Amy Andrews				1
Carl Crown							2	​

 

[saboh12617]

Hello,
Based on the example, with top left cell (row labels) in A1, the formula below should work (with name in M2)

=INDEX($B$1:$I$1, 1, XMATCH(1, FILTER($B$2:$I$4, $A$2:$A$4=M2), 1, -1))

 

[MazH]

Hello,
Based on the example, with top left cell (row labels) in A1, the formula below should work (with name in M2)

=INDEX($B$1:$I$1, 1, XMATCH(1, FILTER($B$2:$I$4, $A$2:$A$4=M2), 1, -1))

Thank you, unfortunately i get a #VALUE! error with this formula:

INDEX(Pivots!$B$31:$AF$31, 1, XMATCH(1, FILTER(Pivots!$B$32:$AF$46, Pivots!$A$32:$A$47='Speech Tracker'!A3), 1, -1))

This is the formula you shared, with the correct references for my spreadsheet...

 

[MazH]

Thank you, unfortunately i get a #VALUE! error with this formula:

INDEX(Pivots!$B$31:$AF$31, 1, XMATCH(1, FILTER(Pivots!$B$32:$AF$46, Pivots!$A$32:$A$47='Speech Tracker'!A3), 1, -1))

This is the formula you shared, with the correct references for my spreadsheet...

***Updated the formula**

Thank you, unfortunately i get a #VALUE! error with this formula:

INDEX(Pivots!$B$31:$AF$31, 1, XMATCH(1, FILTER(Pivots!$B$32:$AF$32, Pivots!$A$32:$A$47='Speech Tracker'!A3), 1, -1))

This is the formula you shared, with the correct references for my spreadsheet...

 

[Fluff]

The first part of the filter should be

Pivots!$B$32:$AF$47

 

[MazH]

The first part of the filter should be

Pivots!$B$32:$AF$47

Thank you that fixed it!

 

[Fluff]

Glad we could help & thanks for the feedback.