Simple INDEX MATCH Question

[FJ]

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.

 

[Ashish Mathur]

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

 

[Mike H]

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

 

[Max]

{=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
---

 

[DILipandey]

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.)
(e-mail address removed)
(e-mail address removed)
New Delhi, India

 

[Max]

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 D27.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

 

[FJ]

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/xlFunctions03.html#IndexMatch4

 

[Max]

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

 

[FJ]

Thanks, everyone, for your help. I think it's clearer now.

 

[Max]

Yes it is. Thanks for ratings.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:23,000 Files:370 Subscribers:66
xdemechanik
---

Thanks, everyone, for your help. I think it's clearer now.

..