Two-Way Lookup Array Formula
 

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