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!

 

[MARZIOTULLIO]

Re: Index Match Help

Can you provide some sample data and what the expected outcome is?

 

[FDibbins]

Re: Index Match Help

the basic INDEX/MATCH will return the 1st it finds, then stop looking.

I think what you need is AVERAGEIFS(), however, Im not sure I understand why you are looking for a range in another range?
=averageifs(range-to-average, criteria1-range, criteria1, criteria2-range, criteria2, criteria3-range, criteria3, ..............)

 

[BWMagee]

Re: Index Match Help

the basic INDEX/MATCH will return the 1st it finds, then stop looking.

I think what you need is AVERAGEIFS(), however, Im not sure I understand why you are looking for a range in another range?
=averageifs(range-to-average, criteria1-range, criteria1, criteria2-range, criteria2, criteria3-range, criteria3, ..............)

Thanks very much. I have adapted it as such:

<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}</style>=AVERAGEIFS('Sheet1'!Q:Q,'Sheet1'!L:L,'Sheet2'!A6,'Sheet1'!H:H,LARGE('Sheet1'!H:H,{1,2,3,4,5}))

First you will see a few changes:
*Now using the AVERAGEIFS formula. Have eliminated the M:M criteria (for now). And changed the MAX function to a LARGE function.

This yields me the result '24', which as you can see from my first post, is the lowest of the five values. But I am after the average, not the lowest, so I'm still doing something wrong. If I change the LARGE function to {1} only, (which represents the highest value in the columns I am searching H:H) it yields the value '24'. If I change to {5} only it yields a value of '48' (which represents the 5th highest value in column H:H). If I change to {3} only, it yields '35' (which represents the 3rd highest value in H:H).

What this means is that the LARGE function is finding the correct values. But for some reason, they are not being averaged together. I wonder what the problem is?

 

[MARZIOTULLIO]

Re: Index Match Help

post 2 please

 

[AhoyNC]

Re: Index Match Help

Maybe you can adjust the example below to meet your needs.
This is an array formula that must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F
1	a	1	t1	21		a
2	a	2	t1	37		t1
3	d	3	y8	94
4	f	4	t1	67		8.8
5	a	5	r	59
6	g	6	r	89
7	a	7	t1	70
8	f	8	r	91
9	a	9	t1	75
10	a	10	t1	24
11	t	11	t1	99
12	a	12	t1	69
13	a	13	t3	53
14	a	14	t1	86
Sheet

 

[BWMagee]

Re: Index Match Help

Can you provide some sample data and what the expected outcome is?

Sheet2

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]I[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Centre[/TD]
[TD]=FORMULA[/TD]
[/TR]
</tbody>[/TABLE]

Sheet1

[TABLE="width: 400"]
<tbody>[TR]
[TD][/TD]
[TD]H[/TD]
[TD]L[/TD]
[TD]Q[/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]6[/TD]
[TD]Centre[/TD]
[TD]48[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4[/TD]
[TD]Office[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]8[/TD]
[TD]Centre[/TD]
[TD]49[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]7[/TD]
[TD]Centre[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]5[/TD]
[TD]Centre[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]3[/TD]
[TD]Hotel[/TD]
[TD]38[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]9[/TD]
[TD]Centre[/TD]
[TD]24[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]2[/TD]
[TD]Office[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD]1[/TD]
[TD]Centre[/TD]
[TD]26[/TD]
[/TR]
</tbody>[/TABLE]

The value I'm looking to return is from Sheet1 ColumnQ.
It is the average of numbers in that column, when:
a) Sheet1 ColumnL = Sheet2 ColumnA, Row6 [ie. "Centre" - L8, L10, L11, L12, L14, L16]
b) The values in Sheet1 ColumnH are the five highest in that column [ie. 9,8,7,6,5 - H8, H10, H11, H12, H14]

Therefore it should be reading from the following rows in Sheet2:
8, 10, 11, 12, 14
Therefore the values in Q it should be averaging are:
48, 49, 45, 35, 24
= 40.2

Thanks!

 

[BWMagee]

Re: Index Match Help

<colgroup><col style="width:30px; "><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"><col style="width:64px;"></colgroup><tbody></tbody>

Spreadsheet Formulas

Cell Formula F4 {=SUMPRODUCT(--($D$1:$D$10000=LARGE(IF(A1:A10000=F1,IF(C1:C10000=F2,D1:D10000)),{1,2,3,4,5}))*(B1:B10000))/5} <tbody> </tbody>

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

<tbody>
</tbody>

Thanks for the reply. It returns a message 'this formula contains an error' and highlights my equivalent to the F1 value in your example.

<style type="text/css">p.p1 {margin: 0.0px 0.0px 0.0px 0.0px; font: 12.0px 'Lucida Grande'; color: #000000}</style>{=SUMPRODUCT(--('Sheet1'!H8:H16=LARGE(IF('Sheet1'!L8:L16='Sheet2'!A6)),{1,2,3,4,5}))*('Sheet1'!Q8:Q116))/5}

 

[AhoyNC]

Re: Index Match Help

See below.

Excel Workbook
	A	B	I
6	Centre		40.2
Sheet2

Excel Workbook
	H	K	L	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
Sheet1

 

[BWMagee]

Re: Index Match Help

Spreadsheet Formulas

Cell Formula I6 {=SUMPRODUCT(--(Sheet1!$H$8:$H$16=LARGE(IF(Sheet1!$L$8:$L$16=Sheet2!$A$6,Sheet1!$H$8:$H$16),{1,2,3,4,5}))*Sheet1!$Q$8:$Q$16)/5} <tbody> </tbody>

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

<tbody>
</tbody>

Works perfectly, thanks!