Excel formulae to find row number of first instance and last of a value in a 2d array

[coldturkey]

Hi all,

I have a table with Dates in a row and times in the columns I need to find the date when the first instance of a particular value occurs. The value can and does occur more than once. I also need a second formula to find the date of the last occurence. For example the first occurence of BRo is 25/11/2020 and the last is 9/12/20. I can't use a UDF as the work environment is locked down. (Note I am running Windows 10 and Excel 2019)
Thanks in advance.

 

[maabadi]

Please Use this:
1. Change $A$2:$A$37 to your Date Columns Range
2. Change $B$2:$E$37 to Your Find (search Range)
For First:

=INDEX($A$2:$A$37,AGGREGATE(15,3,(($B$2:$E$37="BRO")/($B$2:$E$37="BRO")*ROW($B$2:$E$37))-ROW($B$1),1))

For Last:

=INDEX($A$2:$A$37,AGGREGATE(14,3,(($B$2:$E$37="BRO")/($B$2:$E$37="BRO")*ROW($B$2:$E$37))-ROW($B$1),1))

 

[coldturkey]

Please Use this:
1. Change $A$2:$A$37 to your Date Columns Range
2. Change $B$2:$E$37 to Your Find (search Range)
For First:

=INDEX($A$2:$A$37,AGGREGATE(15,3,(($B$2:$E$37="BRO")/($B$2:$E$37="BRO")*ROW($B$2:$E$37))-ROW($B$1),1))

For Last:

=INDEX($A$2:$A$37,AGGREGATE(14,3,(($B$2:$E$37="BRO")/($B$2:$E$37="BRO")*ROW($B$2:$E$37))-ROW($B$1),1))

That works perfectly. Thank you for posting solution so quickly.
I tried to use evaluate formula to see exactly what was going on but it just locks up my CPU for quite a while and then produces the answer without going through any intermediate steps. The formula itself works really quickly.

 

[maabadi]

You 're welcome & thanks for feedback.