Index / Match
 

Hi - I got this function example from the link below, but don't quite
understand the "1" as the match lookup value. Could some help?

=INDEX($A$2:$A$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$2 :$C$7),0))


http://www.contextures.com/xlFunctio...ml#IndexMatch4

Thanks.
tinman

Index / Match
 

I believe the 1 is the value to be matched:
http://www.techonthenet.com/excel/formulas/match.php

Dave
--
Brevity is the soul of wit.


"tinman" wrote:

Hi - I got this function example from the link below, but don't quite
understand the "1" as the match lookup value. Could some help?

=INDEX($A$2:$A$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$2 :$C$7),0))


http://www.contextures.com/xlFunctio...ml#IndexMatch4

Thanks.
tinman

Index / Match
 

When these arrays are multiplied together:

(A10=$B$2:$B$7)*(B10=$C$2:$C$7)

It will return an array of 1 or 0. Like this:

(A10=B2)*(B10=C2) = 0
(A10=B3)*(B10=C3) = 0
(A10=B4)*(B10=C4) = 0
(A10=B5)*(B10=C5) = 1
(A10=B6)*(B10=C6) = 0
(A10=B7)*(B10=C7) = 0

Since the lookup value is 1 a match is found at the 4th position so the
result of the formula is the value in cell A5 which is the 4th cell in the
indexed range.

Biff

"tinman" wrote in message
...
Hi - I got this function example from the link below, but don't quite
understand the "1" as the match lookup value. Could some help?

=INDEX($A$2:$A$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$2 :$C$7),0))


http://www.contextures.com/xlFunctio...ml#IndexMatch4

Thanks.
tinman

Index / Match
 

Thanks Biff...that makes perfect sense...

"Biff" wrote:

When these arrays are multiplied together:

(A10=$B$2:$B$7)*(B10=$C$2:$C$7)

It will return an array of 1 or 0. Like this:

(A10=B2)*(B10=C2) = 0
(A10=B3)*(B10=C3) = 0
(A10=B4)*(B10=C4) = 0
(A10=B5)*(B10=C5) = 1
(A10=B6)*(B10=C6) = 0
(A10=B7)*(B10=C7) = 0

Since the lookup value is 1 a match is found at the 4th position so the
result of the formula is the value in cell A5 which is the 4th cell in the
indexed range.

Biff

"tinman" wrote in message
...
Hi - I got this function example from the link below, but don't quite
understand the "1" as the match lookup value. Could some help?

=INDEX($A$2:$A$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$2 :$C$7),0))


http://www.contextures.com/xlFunctio...ml#IndexMatch4

Thanks.
tinman

Index / Match
 

You're welcome. Thanks for the feedback!

Biff

"tinman" wrote in message
...
Thanks Biff...that makes perfect sense...

"Biff" wrote:

When these arrays are multiplied together:

(A10=$B$2:$B$7)*(B10=$C$2:$C$7)

It will return an array of 1 or 0. Like this:

(A10=B2)*(B10=C2) = 0
(A10=B3)*(B10=C3) = 0
(A10=B4)*(B10=C4) = 0
(A10=B5)*(B10=C5) = 1
(A10=B6)*(B10=C6) = 0
(A10=B7)*(B10=C7) = 0

Since the lookup value is 1 a match is found at the 4th position so the
result of the formula is the value in cell A5 which is the 4th cell in
the
indexed range.

Biff

"tinman" wrote in message
...
Hi - I got this function example from the link below, but don't quite
understand the "1" as the match lookup value. Could some help?

=INDEX($A$2:$A$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$2 :$C$7),0))


http://www.contextures.com/xlFunctio...ml#IndexMatch4

Thanks.
tinman