Find last column in sheet where a string exists in any row

[Kramxel]

Hi All,

Looking for a formula to identify the last column number (reading left to right) where a specific string exists within any row of that column. The columns are labelled by date (i.e. A = 01/01/2015 B = 02/01/2015 etc.) the string in question could exists in any row of that column.

[samba_ravi]

Please attach a sample excel file

[Kramxel]

I have attached a very simplified model for you.

Column A contains names (irrelevant for now)
Row 1 Contains dates.

I have populated a Dummy Task (TASK A) that I would need to identify the last column number it exists in. In the attached file I would expect an answer of 9 (column I) as that is the last column where TASK A exists.

hope this helps!

[samba_ravi]

or try below array formula (Confirm with Ctrl+Shift+Enter)
=MAX(IF($B$2:$I$11=M$2,COLUMN($B$2:$I$11)))

=MAX(IF($B$2:$I$11=M$2,COLUMN($B$2:$I$11)))

[samba_ravi]

=SUMPRODUCT(MAX(($B$2:$I$11=M$2)*COLUMN($B$2:$I$11)))

=SUMPRODUCT(MAX(($B$2:$I$11=M$2)*COLUMN($B$2:$I$11)))

Try this

[Kramxel]

Thank you so much!

both methods work perfectly on the dummy data - is there a performance advantage of one over the other as the live data is significantly larger.