Lookup cell and n cells below - return the average

[merchant60b]

Hi

I am looking for a way to return the average of multiple cells using a vlookup.

This is in respect of sporting prize money. Where a competitor finishes 1st, they get a set prize in $ and I have set the Vlookup to return this figure where I input 1st as their position.

It gets complicated where more than one competitor tie. For example, where 4 players tie for 15th then the prize for each competitor is the average of the prizes for 15th, 16th, 17th and 18th.

If I was to add a column for the number of people joint in said position, is there a way to return the average of those prizes? i.e. vlookup 15th and the n cells below and return the average, where "n" is a figure that I input into a different cell.

Hoping my table will be as follows:

Competitor/Position/Number joint with/Prize

and the prize table, where the vlookup is taken from being:

Position/Prize

Thanks

 

[Aladin Akyurek]

Try to post a small sample (not an image of any kind) along with the expected result for that sample.

 

[merchant60b]

No problem.

Position - Prize

1 - $100,000
...
15 - $10,000
16 - $9,000
17 - $8,000
18 - $7,000
19 - $6,000
20 - $5,000

So the table will read

Competitor - Position - No. Joint - Prize

Competitor A - 1 - 0 - $100,000
Competitor B - 15 - 3 - $8,500 (being the average of positions 15,16,17,18)

 

[Nishant94]

Book1
	A	B	C	D
1	Position	prize
2	1	100000
3	2	90000
4	3	80000
5	4	70000
6	5	60000
7	15	10000
8	16	9000
9	17	8000
10	18	7000
11	19	6000
12	20	5000
13
14
15	Competitor	Position	No. Joint	Prize
16
17	Competitor A	1	0	100000
18	Competitor B	15	3	8500
19	Competitor B	2	2	80000
20
Sheet8

Cell Formulas
Range	Formula
D17	=AVERAGE(INDEX($B$2:$B$12,MATCH(B17,$A$2:$A$12,0)):INDEX($B$2:$B$12,MATCH(B17,$A$2:$A$12,0)+C17))

 

[merchant60b]

	A	B	C	D
1	Position	prize
2	1	100000
3	2	90000
4	3	80000
5	4	70000
6	5	60000
7	15	10000
8	16	9000
9	17	8000
10	18	7000
11	19	6000
12	20	5000
13
14
15	Competitor	Position	No. Joint	Prize
16
17	Competitor A	1	0	100000
18	Competitor B	15	3	8500
19	Competitor B	2	2	80000
20

<colgroup><col style="width: 25pxpx"><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet8​

Worksheet Formulas Cell Formula D17 =AVERAGE(INDEX($B$2:$B$12,MATCH(B17,$A$2:$A$12,0)):INDEX($B$2:$B$12,MATCH(B17,$A$2:$A$12,0)+C17)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Brilliant, thanks v much