Index / Match

[tinman]

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/xlFunctions03.html#IndexMatch4

Thanks.
tinman

 

[Dave F]

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

Dave

 

[Biff]

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]

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

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

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

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