The (A2:A10="Male") conditional is evaluated into an array of TRUE's and
FALSE's, something like {TRUE;TRUE;FALSE;...}. That also happens with
the (B2:B10="Car") conditional. Multiplying these two result arrays
evaluates into an array of 1's and 0's, something like {1;0;1,...} for:
TRUE*TRUE == 1
TRUE*FALSE == 0
FALSE*FALSE == 0
1 is Excel's numeric equivalent of TRUE, 0 of FALSE.
Note that 1 means: both conditions are met.
Given the foregoing we have...
=INDEX(C2:C10,MATCH(1,{1;0;1;...},0))
MATCH with 1 as lookup value, looks up the first instance of 1 in
{1;0;1;...} and returns a position that INDEX uses to fetch the value at
the corresponding position in C2:C10.
Sam via OfficeKB.com wrote:
Hi Everyone,
I've gone through the help Index but cannot completely fathom the Array
Formula syntax below.
Can you help me understand the syntax of this Array Formula?
Column A = Gender
Column B = Vehicle
Column C = Vehicle Model Name
=INDEX(C2:C10,MATCH(1,(A2:A10="Male")*(B2:B10="Car "),0))
What does this do: MATCH(1, ?
Why does the MATCH Function use the number 1?
I presume the Formula will return the Vehicle Model Name from Column C when
a MATCH of both criteria Male and Car is found. However, I do not
understand the usage of MATCH(1, ?
Regards,
Sam
|