If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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/ |
#2
|
|||
|
|||
MATCH within INDEX question
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/ |
#3
|
|||
|
|||
MATCH within INDEX question
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/ |
#4
|
|||
|
|||
MATCH within INDEX question
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/ |
#5
|
|||
|
|||
MATCH within INDEX question
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/ |
#6
|
|||
|
|||
MATCH within INDEX question
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/ |
#7
|
|||
|
|||
MATCH within INDEX question
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/ |
#8
|
|||
|
|||
MATCH within INDEX question
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/ |
#9
|
|||
|
|||
MATCH within INDEX question
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/ |
#10
|
|||
|
|||
MATCH within INDEX question
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/ |
Thread Tools | |
Display Modes | |
|
|