last occurrence of a value in a 4 column array

[kmr47]

I have a spreadsheet with multiple sheets and many columns. Within sheet name TEMPLATE
there are 4 columns S:V that have 2 character initials or blank.
A given initial say "BM" could appear in multiple columns of S:V.

I want to find the last occurrence of the search value "BM" (entered in cell C9) within S:V then
return a date from Column X of that found row.

Using LOOKUP i can find the last occurrence from Column S but cannot make it work
across all four columns S:V

LOOKUP(2,1/(Template!$S5:$V22=$C$9),Template!$X5:$X22)

Any help would be appreciated.

 

[MrRajKumar]

Hi,

Please update your profile to reflect what version of excel you are using. This will really help members to provide answer based on the version you are currently in.

 

[kmr47]

Thank you. I'm using 365 in Windows 10

 

[MrRajKumar]

Try this,

=MAX(IF(Template!$S5:$V22=$C$9,Template!$X5:$X22))

 

[kmr47]

Try this,

=MAX(IF(Template!$S5:$V22=$C$9,Template!$X5:$X22))

Thank you for your time solving my query the solution works perfectly and is appreciated.