Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple INDEX MATCH Question
Hi, I have what is probably a very simple question about an INDEX MATCH array
formula that I found on the Contextures website. In the following formula: {=INDEX($D$2:$D$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$ 2:$C$7),0))} what does the "1" after "MATCH(" indicate? Thanks in advance for any information. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple INDEX MATCH Question
Hi,
The (A10=$B$2:$B$7)*(B10=$C$2:$C$7) portion of the MATCH() function will eveluate to 1 or 0 depending upon whether each condition is true or false. Therefore, if both conditions evaluate to TRUE, then TRUE * TRUE=1. Any other combination will lead to 0. -- Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com "FJ" wrote in message ... Hi, I have what is probably a very simple question about an INDEX MATCH array formula that I found on the Contextures website. In the following formula: {=INDEX($D$2:$D$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$ 2:$C$7),0))} what does the "1" after "MATCH(" indicate? Thanks in advance for any information. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple INDEX MATCH Question
hI,
The 1 is the lookup value for the match function. Imagine this set of data in your table and imagine the formula modified like this which coerces TRUE/FALSE to 1 and 0 =INDEX($D$2:$D$7,MATCH(1,--(A10=$B$2:$B$7)*--(B10=$C$2:$C$7),0)) 1 1 a 2 2 b 3 3 c 4 4 d 99 55 e 5 5 f Matching 99 and 55 Now we are looking up the number 1 so TRUE (Or 1) is returned every time a match is found so we get these 2 arrays 0;0;0;0;1;0 0;0;0;0;1;0 As you will see matching 1 is found only on the fifth element of the array so the fifth element of the INDEX range is returned. Mike "FJ" wrote: Hi, I have what is probably a very simple question about an INDEX MATCH array formula that I found on the Contextures website. In the following formula: {=INDEX($D$2:$D$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$ 2:$C$7),0))} what does the "1" after "MATCH(" indicate? Thanks in advance for any information. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple INDEX MATCH Question
{=INDEX($D$2:$D$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$ 2:$C$7),0))}
what does the "1" after "MATCH(" indicate? 1 = lookup value. It means to search for the exact match of the "1" within the lookup_array: (A10=$B$2:$B$7)*(B10=$C$2:$C$7) which basically reduces to an array of ones/zeros: {0;0;1;0 ..} depending on where the dual conditions are simultaneously satisfied or not. Eg in this case it returns: 3 as the relative position of the "1" within the lookup_array. This "3" is then used by the INDEX($D$2:$D$7, part to return the 3rd element within that indexed range, ie what's in D4 -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple INDEX MATCH Question
I believe "1" is the value which is being looked up here.
So, this formula searches "1" in the range D2 to D7. thanks. -- Dilip Kumar Pandey MBA, BCA, B.Com(Hons.) New Delhi, India "FJ" wrote: Hi, I have what is probably a very simple question about an INDEX MATCH array formula that I found on the Contextures website. In the following formula: {=INDEX($D$2:$D$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$ 2:$C$7),0))} what does the "1" after "MATCH(" indicate? Thanks in advance for any information. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple INDEX MATCH Question
I believe "1" is the value which is being looked up here.
Yes, that's correct So, this formula searches "1" in the range D2 to D7. Afraid not. MATCH(1,lookup_array,0) searches for the exact match of the "1" within the lookup_array: (A10=$B$2:$B$7)*(B10=$C$2:$C$7), not D2:D7. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple INDEX MATCH Question
Hi, everyone, thanks for your replies. Sorry for any confusion my question
might have caused. This is the data that went with the formula: A B C D 1 Code Item Size Price 2 SW001 Sweater Small 10 3 JK001 Jacket Small 30 4 PN001 Pants Small 25 5 SW002 Sweater Med 12 6 JK002 Jacket Med 35 7 PN002 Pants Med 30 8 9 Item Size Price 10 Jacket Med 35 The formula: {=INDEX($D$2:$D$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$ 2:$C$7),0))} was entered in cell C10 and the result was 35. I just wasn't sure exactly what the 1 in the formula stood for, if it was a logical value or something like that. I'm very new to array formulas like these and I just wanted to make sure I understood it correctly. This formula can be found at the following link: http://www.contextures.com/xlFunctio...ml#IndexMatch4 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple INDEX MATCH Question
Hi, everyone, thanks for your replies.
Welcome, but pl mark ALL responses which help answer your query. Do click the YES buttons in those responses. Sorry for any confusion my question might have caused. Think it was clear what you were asking. No confusion. -- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple INDEX MATCH Question
Thanks, everyone, for your help. I think it's clearer now. :)
"FJ" wrote: Hi, I have what is probably a very simple question about an INDEX MATCH array formula that I found on the Contextures website. In the following formula: {=INDEX($D$2:$D$7,MATCH(1,(A10=$B$2:$B$7)*(B10=$C$ 2:$C$7),0))} what does the "1" after "MATCH(" indicate? Thanks in advance for any information. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simple INDEX MATCH Question
Yes it is. Thanks for ratings.
-- Max Singapore http://savefile.com/projects/236895 Downloads:23,000 Files:370 Subscribers:66 xdemechanik --- "FJ" wrote in message ... Thanks, everyone, for your help. I think it's clearer now. :) .. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index Match question. | Excel Worksheet Functions | |||
Simple (?) Match question | Excel Worksheet Functions | |||
Index/match question. | Excel Discussion (Misc queries) | |||
Index/match question. | Excel Worksheet Functions | |||
Match + Index(?) Question | Excel Discussion (Misc queries) |