Two-Way Lookup Array Formula

  • Thread starter Thread starter Sam via OfficeKB.com
  • Start date Start date
S

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
 
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.
 
Back
Top