Finding the first value in an array

[remiral.s]

Hey there, helpful people! I'm pretty new to the Excel formula game that goes beyond the basic SUM functions, and have been trying to practice several different embedded formulas to create a massive statistical sheet of nonsense. All for fun (geez, I need a life...)

Anyway, I've been able to self-teach most of the stuff I've needed thus far, but this problem has me baffled. I am trying to search for a specific value in an array where each row is a different year, with various data in the other columns. What I want to do is find a value in an multi-column, multi-row array, and have the formula return the year that it first appears.

I'd like the formula to search rows left-to-right, then start over on the next row down. The formula I've posted is what I have so far. It successfully returns the year of the first appearing match for (J3) in an array (D2:D29) of only one column. But when I try to expand the search over an array such as (C2:J29) I only get a #N/A return.

Screen shot 2012-03-23 at 3.09.56 PM.png

=VALUE(INDEX(A2:A29,MATCH(T(J3),$D$2:$D$29,0)))

If you download the attachment for more detail, it might clear things up with what I need. With the formula I posted, it searches column D for the first appearance of "North Carolina" and returns 2008. I'd like to expand that over the entire array of C2:J29 and have the formula return the real first appearance of "North Carolina" in 2011.


Thanks in advance for any help. It's much appreciated