F

#### FJ

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.

You are using an out of date browser. It may not display this or other websites correctly.

You should upgrade or use an alternative browser.

You should upgrade or use an alternative browser.

F

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.

A

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

M

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

M

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

---

D

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

M

Yes, that's correct

Afraid not. MATCH(1,lookup_array,0) searches for the exact match of the "1"So, this formula searches "1" in the range D2 to D7.

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

---

F

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

M

Welcome, but pl mark ALL responses which help answer your query.

Do click the YES buttons in those responses.

Think it was clear what you were asking. No confusion.Sorry for any confusion my question might have caused.

--

Max

Singapore

http://savefile.com/projects/236895

Downloads:23,000 Files:370 Subscribers:66

xdemechanik

---

F

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

M

--

Max

Singapore

http://savefile.com/projects/236895

Downloads:23,000 Files:370 Subscribers:66

xdemechanik

---

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

**Want to reply to this thread or ask your own question?**

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.