MATCH within INDEX question

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

I would like to learn exactly what the formula does so I understand it
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))




--
Regards
Frank Kabel
Frankfurt, Germany


---
Message posted from http://www.ExcelForum.com/

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;TR UE;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;TR UE;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)*(S heet2!$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

"Ron H " wrote in message
...
Hello,
In a previous thread I got a nice solution to a lookup problem I had
with the INDEX/MATCH functions.

I would like to learn exactly what the formula does so I understand it
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))




--
Regards
Frank Kabel
Frankfurt, Germany


---
Message posted from http://www.ExcelForum.com/

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 all
conditions being true?
That is to say, does MATCH (2,(... ) give the second occurence of the
condition being true?

Ron



Peo Sjoblom wrote:
*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;TR UE;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;TR UE;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)*(S heet2!$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

"Ron H " wrote in message
...
Hello,
In a previous thread I got a nice solution to a lookup problem I
had
with the INDEX/MATCH functions.

I would like to learn exactly what the formula does so I understand
it
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))




--
Regards
Frank Kabel
Frankfurt, Germany


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/

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

--
Regards
Frank Kabel
Frankfurt, Germany


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 all
conditions being true?
That is to say, does MATCH (2,(... ) give the second occurence of the
condition being true?

Ron



Peo Sjoblom wrote:
*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;TR UE;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;TR UE;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)*(S heet2!$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

"Ron H " wrote in message
...
Hello,
In a previous thread I got a nice solution to a lookup problem I
had
with the INDEX/MATCH functions.

I would like to learn exactly what the formula does so I understand
it 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))




--
Regards
Frank Kabel
Frankfurt, Germany


---
Message posted from http://www.ExcelForum.com/
*


---
Message posted from http://www.ExcelForum.com/

Frank,

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

Ron

(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))


---
Message posted from http://www.ExcelForum.com/

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)


--
Regards
Frank Kabel
Frankfurt, Germany


Frank,

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

Ron

(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))


---
Message posted from http://www.ExcelForum.com/

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 Kabel wrote:
[b]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)


--
Regards
Frank Kabel
Frankfurt, Germany


---
Message posted from http://www.ExcelForum.com/

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 Kabel wrote:
[b]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)


--
Regards
Frank Kabel
Frankfurt, Germany


---
Message posted from http://www.ExcelForum.com/

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 " wrote in message
...
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 Kabel wrote:
[b]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)


--
Regards
Frank Kabel
Frankfurt, Germany


---
Message posted from http://www.ExcelForum.com/

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 Kabel wrote:
[b]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)


--
Regards
Frank Kabel
Frankfurt, Germany


---
Message posted from http://www.ExcelForum.com/