Excel Formula to Search Range of Cells.

You can use this array formula:

=IF(COUNTIF($C$2:$H$2000, $A4)=0, "", INDEX($C$2:$C$2000, MAX(IF(A4=$C$2:$H$2000, ROW($C$2:$H$2000)-1))))

. . . to search a range of cells (in this example C2:H2000) in Excel for one value (given in A4) , and return the value from a given column in that range (in this example column C.).

We look up the names of starting pitchers listed in column A, searching to see if they were in the rotation of top starters for different years listed on each row. The value of the year in column C will be returned for the last reference to the pitcher's name in the range.

As always with an array formula, remember to enter it with CTRL + SHIFT + ENTER.

Thanks to Jerry Beaucaire for the idea posted here.