MATCH within INDEX question

R

Ron H

Hello,
In a previous thread I got a nice solution to a lookup problem I ha
with the INDEX/MATCH functions.

I would like to learn exactly what the formula does so I understand i
completely rather than just copy it in the future.
I understand all apects of the following formula except for one:

What is the "1" for in "MATCH(1,"?


Thank you for any responses.

Ron Hekier

(Portion from previous thread follows)
---------------------------------------

Hi
try the following array formula (entered with CTRL+SHIFT+ENTER):
=INDEX('sheet2'!$E$1:$E$100,MATCH(1,('sheet2'!$A$1
:$A$100=A1)*('sheet2'
!$C$1:$C$100=C1)*('sheet2'!$D$1:$D$100=D1),0))
 
P

Peo Sjoblom

Ok

('sheet2'!$A$1:$A$100=D1)

will return an array of TRUE or FALSE

for example {TRUE;FALSE;FALSE;TRUE;FALSE and so on}

if you multiply a Boolean value with another Boolean value like

{TRUE;FALSE;FALSE;TRUE;FALSE}*{TRUE;FALSE;FALSE;TRUE;FALSE}

the above it will return an array of zeros and ones

{1;0;0;1;0}

TRUE * TRUE = 1 and FALSE * TRUE or TRUE * FALSE or FALSE * FALSE return 0

same with

{TRUE;FALSE;FALSE;TRUE;FALSE}*{TRUE;FALSE;FALSE;TRUE;FALSE}*{TRUE;FALSE;FALS
E;TRUE;FALSE}

{1;0;0;1;0}

TRUE * TRUE * TRUE = 1 while all other combinations are 0

using MATCH(1,{1;0;0;1;0,0) will return the position of the first 1, i.e.
the position
where all three conditions
(Sheet2!$A$1:$A$100=A1)*(Sheet2!$C$1:$C$100=C1)*(Sheet2!$D$1:$D$100=D1)
are TRUE..


HTH

--


No private emails please, for everyone's
benefit keep the discussion in the newsgroup.

Regards,

Peo Sjoblom
 
R

Ron H

So what other options are there for that position?
Can I write MATCH(0,(...
MATCH (2,(...

Will that give me an occurence of other than the first for al
conditions being true?
That is to say, does MATCH (2,(... ) give the second occurence of th
condition being true?

Ron
 
F

Frank Kabel

Hi
the first parameter of MATCH is the value you're looking for. It has
nothing to do with the position of your match. See the Excel help for
MATCH on this syntax
 
R

Ron H

Frank,

I have looked up the MATCH function in Excel help and several referenc
books but I am still confused.
I know the first number is the lookup value, but in this case am
looking up the number "1"? (I didn't think I was.)

Ron
 
F

Frank Kabel

Hi
in this case '1' is the same as 'TRUE'. e.g. you are multiplying 3
conditions:
('sheet2'!$A$1:$A$100=A1)*('sheet2'!$C$1:$C$100=C1)*('sheet2'!$D$1:$D$1
00=D1)

This part returns only '1' if all conditions are met (TRUE=1 / FALSE=0)
 
R

Ron H

MATCH(1,('sheet2'!$A$1"
:$A$100=A1)*('sheet2'
!$C$1:$C$100=C1)*('sheet2'!$D$1:$D$100=D1),0))


So in this case, the "lookup value" which I will call x i
MATCH(x,....) is 1 which is logical "TRUE" and the function is TRUE i
the lookup array ('sheet2'!$A$1"
:$A$100=A1)*('sheet2'
!$C$1:$C$100=C1)*('sheet2'!$D$1:$D$100=D1) equals it, that is to say i
TRUE?

I undestand how it needs to have TRUE*TRUE*TRUE in order to be TRUE.
My question is does that "1" in "MATCH (1", mean that the function i
looking for that value (1 or TRUE) in the lookup array?

Ron Hekier


Frank said:
Hi
in this case '1' is the same as 'TRUE'. e.g. you are multiplying 3
conditions:
('sheet2'!$A$1:$A$100=A1)*('sheet2'!$C$1:$C$100=C1)*('sheet2'!$D$1:$D$1
00=D1)

This part returns only '1' if all conditions are met (TRUE=1
FALSE=0)
Click to expand...
 
R

Ron H

MATCH(1,('sheet2'!$A$1"
:$A$100=A1)*('sheet2'
!$C$1:$C$100=C1)*('sheet2'!$D$1:$D$100=D1),0))


So in this case, the "lookup value" which I will call x in
MATCH(x,....) is 1 which is logical "TRUE" and the function is TRUE if
the lookup array ('sheet2'!$A$1"
:$A$100=A1)*('sheet2'
!$C$1:$C$100=C1)*('sheet2'!$D$1:$D$100=D1) equals it, that is to say is
TRUE?

I undestand how it needs to have TRUE*TRUE*TRUE in order to be TRUE.
My question is does that "1" in "MATCH (1", mean that the function is
looking for that value (1 or TRUE) in the lookup array?

Ron Hekier


Frank said:
Hi
in this case '1' is the same as 'TRUE'. e.g. you are multiplying 3
conditions:
('sheet2'!$A$1:$A$100=A1)*('sheet2'!$C$1:$C$100=C1)*('sheet2'!$D$1:$D$1
00=D1)

This part returns only '1' if all conditions are met (TRUE=1 /
FALSE=0)
Click to expand...
 
R

Ragdyer

Let me try to re-phrase this so that perhaps, it may be more understandable,
even though it may not be technically correct.

It (Match) is *not* looking for *anything* in the lookup array!

It's looking for the condition "True", in the arguments of the function.
When it finds "it" (True), it then does what Match() does, and returns the
location of where the individual arguments first found (matched) *all* the
stipulated conditions set forth in each individual argument.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------


Ron H > said:
MATCH(1,('sheet2'!$A$1"
:$A$100=A1)*('sheet2'
!$C$1:$C$100=C1)*('sheet2'!$D$1:$D$100=D1),0))


So in this case, the "lookup value" which I will call x in
MATCH(x,....) is 1 which is logical "TRUE" and the function is TRUE if
the lookup array ('sheet2'!$A$1"
:$A$100=A1)*('sheet2'
!$C$1:$C$100=C1)*('sheet2'!$D$1:$D$100=D1) equals it, that is to say is
TRUE?

I undestand how it needs to have TRUE*TRUE*TRUE in order to be TRUE.
My question is does that "1" in "MATCH (1", mean that the function is
looking for that value (1 or TRUE) in the lookup array?

Ron Hekier


Frank said:
Hi
in this case '1' is the same as 'TRUE'. e.g. you are multiplying 3
conditions:
('sheet2'!$A$1:$A$100=A1)*('sheet2'!$C$1:$C$100=C1)*('sheet2'!$D$1:$D$1
00=D1)

This part returns only '1' if all conditions are met (TRUE=1 /
FALSE=0)
Click to expand...
Click to expand...
 
F

Frank Kabel

Hi Ron
that is correct. QA '1' is returned if all 3 conditions are TRUE. So
that
TRUE*TRUE*TRUE=1

Note: it returns the FIRST instance of such a match in your data range


--
Regards
Frank Kabel
Frankfurt, Germany

MATCH(1,('sheet2'!$A$1"
:$A$100=A1)*('sheet2'
!$C$1:$C$100=C1)*('sheet2'!$D$1:$D$100=D1),0))


So in this case, the "lookup value" which I will call x in
MATCH(x,....) is 1 which is logical "TRUE" and the function is TRUE if
the lookup array ('sheet2'!$A$1"
:$A$100=A1)*('sheet2'
!$C$1:$C$100=C1)*('sheet2'!$D$1:$D$100=D1) equals it, that is to say
is TRUE?

I undestand how it needs to have TRUE*TRUE*TRUE in order to be TRUE.
My question is does that "1" in "MATCH (1", mean that the function is
looking for that value (1 or TRUE) in the lookup array?

Ron Hekier


Frank said:
Hi
in this case '1' is the same as 'TRUE'. e.g. you are multiplying 3
conditions:
('sheet2'!$A$1:$A$100=A1)*('sheet2'!$C$1:$C$100=C1)*('sheet2'!$D$1:$D$1
00=D1)

This part returns only '1' if all conditions are met (TRUE=1 /
FALSE=0)
Click to expand...
Click to expand...
 
Top