Index Match Issue

[BWMagee]

Hi all, I'm using two Index Match formulas in adjacent cells. They are slightly different as I want them to return a value from different ranges, but they are written in exactly the same manner, yet one works and one doesn't.

This works:

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #006107}span.s2 {color: #0057d6}span.s3 {color: #ab30d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}span.s6 {color: #ff9c1b}span.s7 {color: #fe4fdd}</style>{=(IF($E86=$N$82,INDEX($N$131:$N$454,MATCH(1,($C86=$C$131:$C$454)*($D86=$D$131:$D$454),0)),0)}

This doesn't (it returns 0, as per the "value_if_false" number at the end):

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #0057d6}span.s2 {color: #006107}span.s3 {color: #ab30d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}span.s6 {color: #ff9c1b}span.s7 {color: #fe4fdd}</style>
{=IF($E$131:$E$454=$P$82,INDEX($P$83:$P$120,MATCH(1,($C86=$C$131:$C$454)*($D86=$D$131:$D$454),0)),0)}

There are three differences that I can see:
1) The IF function in example two looks in a range, as opposed to the exact cell of example one.
2) The IF function references cell N82, as opposed to P82.
2) The INDEX range is different.

These are all on purpose. The range P83-P120 does contain some #DIV/0! cells, so I enclosed example two with an IFERROR function to see if that would help, but it returned the same value 0, so it had no effect.

{=IFERROR(IF($E$131:$E$454=$P$82,INDEX($P$83:$P$120,MATCH(1,($C86=$C$131:$C$454)*($D86=$D$131:$D$454),0)),0),0)

The only other thing I can think of is that INDEX MATCH might not like the differing ranges in example two. (ie. 83:120 not being the same size as the others 131:454). So I experimented changing the range, but it had no effect, still returning the same sorry 0.

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #006107}span.s2 {color: #0057d6}span.s3 {color: #ab30d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}span.s6 {color: #ff9c1b}span.s7 {color: #fe4fdd}</style>
=IFERROR(IF($E$131:$E$454=$P$82,INDEX($P$131:$P$454,MATCH(1,($C86=$C$131:$C$454)*($D86=$D$131:$D$454),0)),0),0)

So I'm at a loss as to why the first one works but the second doesn't. Any ideas are much appreciated.

 

[steve the fish]

You have a proper muddle here. Ignore the formulas. What are trying to do? Why isnt the $E$131:$E$454=$P$82 within the match formula? The index range length cant be different from the match range length. It makes no sense to do that as any match past the first 40 or so cells would result in a reference error.

 

[BWMagee]

You have a proper muddle here. Ignore the formulas. What are trying to do? Why isnt the $E$131:$E$454=$P$82 within the match formula? The index range length cant be different from the match range length. It makes no sense to do that as any match past the first 40 or so cells would result in a reference error.

Thanks for the reply.


[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]P[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]=Formula[/TD]
[/TR]
[TR]
[TD]82[/TD]
[TD][/TD]
[TD][/TD]
[TD]T6[/TD]
[TD]B4[/TD]
[/TR]
[TR]
[TD]86[/TD]
[TD]Home[/TD]
[TD]Office[/TD]
[TD][/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]310[/TD]
[TD]Home[/TD]
[TD]Office[/TD]
[TD]B4[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]454[/TD]
[TD]Yard[/TD]
[TD]Hotel[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Result should be P86 ("35")
Criteria:
If E310 ("B4") = P82 ("B4")
If D82 ("Office") = D310 ("Office")
If C82 ("Home") = C310 ("Home")

The reason I included an IF statement outside the INDEX MATCH is
a) It worked. In the first example at least.
b) I am actually combining multiple index matches in one formula. Eg. IF(INDEX(MATCH....,IF(INDEX(MATCH.... Using the IF statement allows me to structure it so that IF, INDEX MATCH = True, return the cell eg. P86. IF INDEX MATCH = False, then the next IF(INDEX(MATCH.... and the last one of these ends in a 0. Meaning if they are all false, return 0. This is what is currently happening. I'm getting the 0 (in the second example, but not the first, which works).

 

[BWMagee]

Note that "B4" (In column E) won't necessarily be in E310. It could be anywhere from E131:E454.
The second row of "Home" and "Office" (row 310) could appear anywhere from C&D131:454
The first row of "Home" and "Office" (row 86) could appear anywhere from C&D83:120
The result "35" could be anywhere from P83:P120. And its important not to set a range lower than that is there is data down there I definitely don't want included.
The "B4" (In column P) will always be at P82.

 

[BWMagee]

It's OK, I have found a solution by shifting data around.

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}span.s1 {color: #0057d6}span.s2 {color: #006107}span.s3 {color: #ab30d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}span.s6 {color: #ff9c1b}span.s7 {color: #fe4fdd}</style>IF($P$82=$E6,INDEX($P$83:$P$120,MATCH(1,($C6=$C$83:$C$120)*($D6=$D$83:$D$120),0)))

They key seems to be making the INDEX and MATCH ranges the same, as you say, and making P82 = a single cell, rather than a range of cells.