Two-Way Lookup Array Formula

[Sam via OfficeKB.com]

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

 

[Aladin Akyurek]

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]

Hi Aladin,

Thank you so much for very clear explanation.


Regards,
Sam