Index Match

[BWMagee]

Hi, I am using the INDEX, MATCH combination with a number of criteria, but am receiving an incorrect result.

In plain english, here is what I am trying to do.

The formula is entered into "Sheet2" column D.
The value I wish to return is an average of "Sheet1" column Q.
Based on the following criteria:
a) The value in "Sheet2" column A, is found in "Sheet1" column L
b) The value in "Sheet2" column B, is found in "Sheet1" column M
c) The value in "Sheet1" column H, is one of the 5 highest values in that column

I've tried a number of variations without success, but here is the latest formula used:

<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: #ab30d6}span.s3 {color: #a54a29}span.s4 {color: #0057d6}span.s5 {color: #33af4a}span.s6 {color: #ff9c1b}</style>{=AVERAGE(INDEX('Sheet1'!Q:Q,MATCH(1,('Sheet1'!L:L='Sheet2'!A:A)*('Sheet1'!M:M1='Sheet2'!B:B)*(('Sheet1'!H:H>=MAX('Sheet1'!H:H)-5)),0)))}

This returns an answer of '48'.
'48' is the first value in "Sheet1" column Q that meets the three criteria (a, b, c). However, it is not the average. The average should be 40.2 =(48+49+35+45+24)/5.
There are many more values than those in column Q. But those are the 5 that meet the three criteria.

Any help is much appreciated!

 

[AhoyNC]

Re: Index Match Help

You're welcome. Thanks for the feedback.

 

[BWMagee]

Re: Index Match Help

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; line-height: 16.0px; font: 13.0px Verdana; color: #222222 ; -webkit-text-stroke: #222222 }p.p2 {margin: 0.0px 0.0px 0.0px 0.0px; line-height: 16.0px; font: 13.0px Verdana; color: #222222 ; -webkit-text-stroke: #222222 ; min-height: 16.0px}p.p3 {margin: 0.0px 0.0px 0.0px 0.0px; line-height: 16.0px; font: 13.0px Verdana; color: #222222 ; -webkit-text-stroke: #222222 ; background-color: #ffffff }p.p4 {margin: 0.0px 0.0px 0.0px 0.0px; line-height: 16.0px; font: 13.0px Verdana; color: #222222 ; -webkit-text-stroke: #222222 ; background-color: #ffffff ; min-height: 16.0px}span.s1 {font-kerning: none; background-color: #ffffff }span.s2 {font-kerning: none}</style>

You're welcome. Thanks for the feedback.

Unfortunately I have now discovered an error. From our example, in Column H you will see that the top 5 values all belong to “Centre” in Column L (9,8,7,6,5), and no other row has a value equal to those. However, adding in more data on my spreadsheet, other rows now have values equal to those (ie. 9,8,7,6,5), and when they do, it gets added into the Q average, despite not fitting our criteria.


Eg. we want to average Q from H14, H10, H11, H8, H12, to give an average of 40.2
But if we change the value in H9 from “4” to “5”, it is now inside the largest five values for that column, and it’s value in Q “27” gets added into the formula, for an average of 45.6


This shouldn’t be happening, as we stated in our IF function that L = A6 (“Centre”). So nothing named “Office” should be included, no matter what number exists in H, and yet, if we change H9 from “4” to “5”, “27” in Q becomes included.


Any ideas?

 

[MARZIOTULLIO]

Re: Index Match Help

	H​	I​	J​	K​	L​	M​	N​	O​	P​	Q​
8​	6​				Centre					48​
9​	4​				Office					27​
10​	8​				Centre					49​
11​	7​				Centre					45​
12​	5​				Centre					35​
13​	3​				Hotel					38​
14​	9​				Centre					24​
15​	2​				Office					21​
16​	1​				Centre					26​
17​
18​		average
19​		40.2​

<tbody>
</tbody>

I19=AVERAGEIFS(Q8:Q16,L8:L16,L8,H8:H16,">="&SMALL(H8:H16,5))

 

[BWMagee]

Re: Index Match Help

I19=AVERAGEIFS(Q8:Q16,L8:L16,L8,H8:H16,">="&SMALL(H8:H16,5))

Interesting that you used SMALL instead of LARGE, when it is the LARGE I'm looking for. But it worked, so thanks very much!

 

[AhoyNC]

Re: Index Match Help

If your data is going to be increasing in rows then I think you will have a problem using the SMALL function.

In the example below the 5th smallest value is 5, so the formula will average any values over 5 for Centre. So in this example you would get an average of the top 8 values and not the top 5.

Excel Workbook
	H	L	M	Q
4			Average
5		Centre	36
6
7
8	6	Centre		48
9	5	Office		27
10	8	Centre		49
11	7	Centre		45
12	5	Centre		35
13	3	Hotel		38
14	9	Centre		24
15	2	Office		21
16	3	Centre		26
17	10	Centre		30
18	1	Centre		21
19	12	Centre		``
20	13	Centre		21
Sheet

 

[AhoyNC]

Re: Index Match Help

Here is something else to try:

Excel Workbook
	A	H	I
6	Centre		29
Sheet2

Excel Workbook
	H	L	M	Q
8	6	Centre		48
9	28	Office		27
10	8	Centre		49
11	7	Centre		45
12	5	Centre		35
13	5	Hotel		38
14	9	Centre		24
15	2	Office		21
16	3	Centre		26
17	3	Centre		30
18	1	Centre		21
19	12	Centre		6
20	13	Centre		21
Sheet1

 

[BWMagee]

Re: Index Match Help

If your data is going to be increasing in rows then I think you will have a problem using the SMALL function.

In the example below the 5th smallest value is 5, so the formula will average any values over 5 for Centre. So in this example you would get an average of the top 8 values and not the top 5.

Here is something else to try:

Spreadsheet Formulas

Cell Formula I6 {=SUMPRODUCT(--(Sheet1!L8:L20=$A$6)*(--(LARGE(IF(Sheet1!$L$8:$L$20=A6,Sheet1!H8:H20),{1,2,3,4,5})=Sheet1!H8:H20))*Sheet1!Q8:Q20)/5} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Thanks for the detailed responses, much appreciated. Yes, in the end I wasn't sure about SMALL, but found a way to adapt AVERAGE(IF nests to use LARGE {1,2,3,4,5} in a way that worked. It combined advice from both yourself and Marzio in the end. Now I have it working I don't want to break it, so will stick with whats there, but if it comes up with any errors, its good to have this new SUMPRODUCT as backup. Thanks!

 

[AhoyNC]

Re: Index Match Help

Glad you got it worked out. I agree if you have something working best not to change it. What was the formula you came up with in the end?

 

[BWMagee]

Re: Index Match Help

Glad you got it worked out. I agree if you have something working best not to change it. What was the formula you came up with in the end?

Well, it was this:
<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px Verdana; color: #424242}span.s1 {color: #006107}span.s2 {color: #0057d6}span.s3 {color: #ab30d6}span.s4 {color: #a54a29}span.s5 {color: #33af4a}span.s6 {color: #ff9c1b}</style>=AVERAGE(IF('2018F'!$L$2:$L$6755=PvO!$C149,IF('2018F'!$M$2:$M$6755=PvO!$D149,IF('2018F'!$R$2:$R$6755>=55,IF(LARGE('2018F'!$H$2:$H$6755,{1,2,3,4,5}),'2018F'!$S$2:$S$6755-'2018F'!$Z$2:$Z$6755)))))*$F149

But it appears now not to be working - returning only the average according to the other criteria, but not the largest five values from column H. I attempted your new formula, but received #NUM !

<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}</style>
{=SUMPRODUCT(--('2018F'!$L$2:$L$6755=$B6)*(--(LARGE(IF('2018F'!$L$2:$L$6755=$B6,'2018F'!$H$2:$H$6755),{1,2,3,4,5})='2018F'!$H$2:$H$6755))*'2018F'!$Q$2:$Q$6755)/5}