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

[daddylonglegs]

Hello remiral.s welcome to excelforum

MATCH will only work with a single column or row, try this "array formula"

=MAX(IF(C$2:I$29=J3,A$2:A$29))

confirmed with CTRL+SHIFT+ENTER

[remiral.s]

Worked beautifully, thanks. What does the CTRL-SHIFT-ENTER imply, exactly? I see it adds braces, but how did the program know to search the if_true array for the corresponding year by applying the array formula to the max formula?

Still new to array formulas, so I've only used them a couple times and just accepted them for working, but it would be helpful to know their actual purpose for future applications. Thanks again, daddylonglegs.

[daddylonglegs]

Normally an If function applies to a single cell, e.g. something like

=IF(C2=F3,A2)

....but if we want to evaluate a whole range like

=IF(C2:I29=F3,A2:A29)

then CTRL+SHIFT+ENTER effectively tells excel it must loop through the whole range.

The result of that IF function is therefore the corresponding year for every match.....and FALSE for every non-match (try selecting that part of the formula in the formula bar and pressing F9 and you'll see the array that the formula returns.

MAX then returns the maximum value from that array, FALSE values are ignored so you simply get the latest year