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

--
Message posted via http://www.officekb.com

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

Hi Aladin,

Thank you so much for very clear explanation.


Regards,
Sam

--
Message posted via http://www.officekb.com