Need clarification on why my formula works
 

Yep, it works. But I'm not sure I understand exactly HOW.

Here's the formula:

=INDEX($T$2:$T$9999,MATCH(1,(A2=$R$2:$R$9999)*(B2= $S$2:$S$9999),0)))

I understand the Index part but what's throwing me is the Match. I've read
all I can find on Match in the help screens and I can't find anything that
deals with the multiplication that is in my Match.

I know it's returning the row number for the Index but I don't understand
HOW it's doing that.

Thanks for any help!!

Lauri S.

Need clarification on why my formula works
 

When A2 matches your R range, the value is TRUE; and when B2 matches your Q
range the value is also TRUE. The multiplcation changes the TRUE to 1
(False=0) so when a row is found when A2 & B2 match in R & S, then 1*1=1
which meets the Match lookup value of 1.

If there are several rows where there is a match, you will get the first
occurence.

Put this data in columns A to C and this formula in D1:

=INDEX(A1:A4,MATCH(1,(B1:B4=2)*(C1:C4=2),0))

Entered with Ctrl+Shift+Enter

Change the match values in the formula from 2 & 2 to 1 & 1 and see the result.



A 1 2
B 2 2
C 3 5
D 1 1


HTH

"LauriS" wrote:

Yep, it works. But I'm not sure I understand exactly HOW.

Here's the formula:

=INDEX($T$2:$T$9999,MATCH(1,(A2=$R$2:$R$9999)*(B2= $S$2:$S$9999),0)))

I understand the Index part but what's throwing me is the Match. I've read
all I can find on Match in the help screens and I can't find anything that
deals with the multiplication that is in my Match.

I know it's returning the row number for the Index but I don't understand
HOW it's doing that.

Thanks for any help!!

Lauri S.

Need clarification on why my formula works
 

These 2 arrays are multiplied together:

(A2=$R$2:$R$9999)
(B2=$S$2:$S$9999)

The result of this multiplication is an array of 1s and 0s. This is how that
would look:

(A2=R2) = FALSE * (B2=S2) = FALSE
FALSE * FALSE = 0

(A2=R3) = TRUE * (B2=S3) = TRUE
TRUE * TRUE = 1

It does this for each element.

Now you have an array of 1s and 0s:

0
1
0
0
0

MATCH is looking for the lookup_value of 1:

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

1 is a match and it's in the 2nd position of the lookup_array. The 2 is then
passed to the INDEX function:

=INDEX(T2:T9999,2)

The result of the formula is the value in cell T3.


--
Biff
Microsoft Excel MVP


"LauriS" wrote in message
...
Yep, it works. But I'm not sure I understand exactly HOW.

Here's the formula:

=INDEX($T$2:$T$9999,MATCH(1,(A2=$R$2:$R$9999)*(B2= $S$2:$S$9999),0)))

I understand the Index part but what's throwing me is the Match. I've
read
all I can find on Match in the help screens and I can't find anything that
deals with the multiplication that is in my Match.

I know it's returning the row number for the Index but I don't understand
HOW it's doing that.

Thanks for any help!!

Lauri S.

Need clarification on why my formula works
 

Thanks, Biff. What I was missing was the fact that the arrays return a LIST
of answers - not just one.

Now it makes perfect sense!

Lauri S.

"T. Valko" wrote:

These 2 arrays are multiplied together:

(A2=$R$2:$R$9999)
(B2=$S$2:$S$9999)

The result of this multiplication is an array of 1s and 0s. This is how that
would look:

(A2=R2) = FALSE * (B2=S2) = FALSE
FALSE * FALSE = 0

(A2=R3) = TRUE * (B2=S3) = TRUE
TRUE * TRUE = 1

It does this for each element.

Now you have an array of 1s and 0s:

0
1
0
0
0

MATCH is looking for the lookup_value of 1:

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

1 is a match and it's in the 2nd position of the lookup_array. The 2 is then
passed to the INDEX function:

=INDEX(T2:T9999,2)

The result of the formula is the value in cell T3.


--
Biff
Microsoft Excel MVP


"LauriS" wrote in message
...
Yep, it works. But I'm not sure I understand exactly HOW.

Here's the formula:

=INDEX($T$2:$T$9999,MATCH(1,(A2=$R$2:$R$9999)*(B2= $S$2:$S$9999),0)))

I understand the Index part but what's throwing me is the Match. I've
read
all I can find on Match in the help screens and I can't find anything that
deals with the multiplication that is in my Match.

I know it's returning the row number for the Index but I don't understand
HOW it's doing that.

Thanks for any help!!

Lauri S.

Need clarification on why my formula works
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"LauriS" wrote in message
...
Thanks, Biff. What I was missing was the fact that the arrays return a
LIST
of answers - not just one.

Now it makes perfect sense!

Lauri S.

"T. Valko" wrote:

These 2 arrays are multiplied together:

(A2=$R$2:$R$9999)
(B2=$S$2:$S$9999)

The result of this multiplication is an array of 1s and 0s. This is how
that
would look:

(A2=R2) = FALSE * (B2=S2) = FALSE
FALSE * FALSE = 0

(A2=R3) = TRUE * (B2=S3) = TRUE
TRUE * TRUE = 1

It does this for each element.

Now you have an array of 1s and 0s:

0
1
0
0
0

MATCH is looking for the lookup_value of 1:

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

1 is a match and it's in the 2nd position of the lookup_array. The 2 is
then
passed to the INDEX function:

=INDEX(T2:T9999,2)

The result of the formula is the value in cell T3.


--
Biff
Microsoft Excel MVP


"LauriS" wrote in message
...
Yep, it works. But I'm not sure I understand exactly HOW.

Here's the formula:

=INDEX($T$2:$T$9999,MATCH(1,(A2=$R$2:$R$9999)*(B2= $S$2:$S$9999),0)))

I understand the Index part but what's throwing me is the Match. I've
read
all I can find on Match in the help screens and I can't find anything
that
deals with the multiplication that is in my Match.

I know it's returning the row number for the Index but I don't
understand
HOW it's doing that.

Thanks for any help!!

Lauri S.