index function without row & col (comma only)
 

Can someone explain what it does - index(array,)

e.g.
INDEX(($A$2:$A$100=L1)*($B$2:$B$100=M1),)

Thanks.
--
Barry

index function without row & col (comma only)
 

INDEX(($A$2:$A$100=L1)*($B$2:$B$100=M1),)
Can someone explain what it does

It returns a vertical array of 1s and 0s.

Let's assume L1 = x and M1 = y.

......A.....B
2...x......y
3...z......z
4...x.....y
5...x.....x

(A2=L1)*(B2=M1)
(A3=L1)*(B3=M1)
(A4=L1)*(B4=M1)
(A5=L1)*(B5=M1)

TRUE*TRUE = 1
FALSE*FALSE = 0
TRUE*TRUE = 1
FALSE*FALSE = 0

INDEX({1;0;1;0},)

The comma means the row_num argument has been omitted so it defaults to 0
which means to return the entire array. Why the formula is written that way
depends on how it's being used.

--
Biff
Microsoft Excel MVP


"loba" wrote in message
...
Can someone explain what it does - index(array,)

e.g.
INDEX(($A$2:$A$100=L1)*($B$2:$B$100=M1),)

Thanks.
--
Barry

index function without row & col (comma only)
 

TRUE*TRUE = 1
FALSE*FALSE = 0
TRUE*TRUE = 1
FALSE*FALSE = 0

Actually, the array would be:

TRUE*TRUE = 1
FALSE*FALSE = 0
TRUE*TRUE = 1
TRUE*FALSE = 0

--
Biff
Microsoft Excel MVP


"T. Valko" wrote in message
...
INDEX(($A$2:$A$100=L1)*($B$2:$B$100=M1),)
Can someone explain what it does

It returns a vertical array of 1s and 0s.

Let's assume L1 = x and M1 = y.

.....A.....B
2...x......y
3...z......z
4...x.....y
5...x.....x

(A2=L1)*(B2=M1)
(A3=L1)*(B3=M1)
(A4=L1)*(B4=M1)
(A5=L1)*(B5=M1)

TRUE*TRUE = 1
FALSE*FALSE = 0
TRUE*TRUE = 1
FALSE*FALSE = 0

INDEX({1;0;1;0},)

The comma means the row_num argument has been omitted so it defaults to 0
which means to return the entire array. Why the formula is written that
way depends on how it's being used.

--
Biff
Microsoft Excel MVP


"loba" wrote in message
...
Can someone explain what it does - index(array,)

e.g.
INDEX(($A$2:$A$100=L1)*($B$2:$B$100=M1),)

Thanks.
--
Barry