Index, match with large function (two step) with multiple criteria pulling wrong record

[mclary]

I have a spreadsheet that is supposed to find the top three players that meet two criteria (position (set in cell $K$4) and salary (set in cell $K$5) (less than or equal to a desired amount) that have the highest projected points.

My first formula seems to work. This finds the highest three average projections based on the two criteria set forth above. The formula is:

in cell L7: {=LARGE(IF(Price_List[Pos]=$K$4,IF(Price_List[Salary]<=$K$5,Price_List[Avg Projection])),I7)}

My next formula which is supposed to pull the corresponding player's name is not working. It pulls the first record in the list whose average projection matches the value that my first formula pulled). I need to make it find the player that matches the avg projection value determined in the first formula but also matches the position and salary criteria. I am using an index match as follows:

in cell J7: =INDEX(Price_List[Player],MATCH(L7,Price_List[Avg Projection],0))

I tried to paste the spreadsheet below but it is pretty ugly.

Can anyone help me figure out what the second formula should be?


[TABLE="width: 866"]
<colgroup><col><col><col><col><col><col><col span="7"></colgroup><tbody>[TR]
[TD]Player[/TD]
[TD]o[/TD]
[TD]Pos[/TD]
[TD]Matchup[/TD]
[TD]Salary[/TD]
[TD]Avg Projection[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]QB1[/TD]
[TD]o[/TD]
[TD]QB[/TD]
[TD]N/A[/TD]
[TD]7700[/TD]
[TD]21.47[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]QB20[/TD]
[TD]o[/TD]
[TD]QB[/TD]
[TD]N/A[/TD]
[TD]5500[/TD]
[TD]17.77[/TD]
[TD][/TD]
[TD][/TD]
[TD="colspan: 4"]Salary Optimizer[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]QB30[/TD]
[TD]o[/TD]
[TD]QB[/TD]
[TD]N/A[/TD]
[TD]4700[/TD]
[TD]16.10[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]Position[/TD]
[TD]WR[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RB3[/TD]
[TD]o[/TD]
[TD]RB[/TD]
[TD]N/A[/TD]
[TD]8000[/TD]
[TD]19.93[/TD]
[TD][/TD]
[TD][/TD]
[TD] [/TD]
[TD]Salary[/TD]
[TD]$7,000[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RB4[/TD]
[TD]o[/TD]
[TD]RB[/TD]
[TD]N/A[/TD]
[TD]7700[/TD]
[TD]19.77[/TD]
[TD][/TD]
[TD][/TD]
[TD]Rank[/TD]
[TD]Name[/TD]
[TD]Salary[/TD]
[TD]Avg Projection[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RB35[/TD]
[TD]o[/TD]
[TD]RB[/TD]
[TD]N/A[/TD]
[TD]4500[/TD]
[TD]10.70[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]QB20[/TD]
[TD="align: right"]$5,500[/TD]
[TD="align: right"]17.8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RB36[/TD]
[TD]o[/TD]
[TD]RB[/TD]
[TD]N/A[/TD]
[TD]4500[/TD]
[TD]11.80[/TD]
[TD][/TD]
[TD][/TD]
[TD]2[/TD]
[TD]WR24[/TD]
[TD="align: right"]$5,900[/TD]
[TD="align: right"]16.1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RB37[/TD]
[TD]o[/TD]
[TD]RB[/TD]
[TD]N/A[/TD]
[TD]4500[/TD]
[TD]5.33[/TD]
[TD][/TD]
[TD][/TD]
[TD]3[/TD]
[TD]WR30[/TD]
[TD="align: right"]$5,600[/TD]
[TD="align: right"]15.9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RB66[/TD]
[TD]o[/TD]
[TD]RB[/TD]
[TD]N/A[/TD]
[TD]3800[/TD]
[TD]2.80[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]RB67[/TD]
[TD]o[/TD]
[TD]RB[/TD]
[TD]N/A[/TD]
[TD]3800[/TD]
[TD]6.37[/TD]
[TD][/TD]
[TD="colspan: 2"]Projection Formula[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WR20[/TD]
[TD]o[/TD]
[TD]WR[/TD]
[TD]N/A[/TD]
[TD]6300[/TD]
[TD]15.53[/TD]
[TD][/TD]
[TD="colspan: 6"]{=LARGE(IF(Price_List[Pos]=$K$4,IF(Price_List[Salary]<=$K$5,Price_List[Avg Projection])),I7)}[/TD]
[/TR]
[TR]
[TD]WR21[/TD]
[TD]o[/TD]
[TD]WR[/TD]
[TD]N/A[/TD]
[TD]6200[/TD]
[TD]17.77[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WR22[/TD]
[TD]o[/TD]
[TD]WR[/TD]
[TD]N/A[/TD]
[TD]6100[/TD]
[TD]13.43[/TD]
[TD][/TD]
[TD="colspan: 2"]Name Formula[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WR23[/TD]
[TD]o[/TD]
[TD]WR[/TD]
[TD]N/A[/TD]
[TD]6000[/TD]
[TD]3.50[/TD]
[TD][/TD]
[TD="colspan: 6"]=INDEX(Price_List[Player],MATCH(L7,Price_List[Avg Projection],0))[/TD]
[/TR]
[TR]
[TD]WR24[/TD]
[TD]o[/TD]
[TD]WR[/TD]
[TD]N/A[/TD]
[TD]5900[/TD]
[TD]16.13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WR29[/TD]
[TD]o[/TD]
[TD]WR[/TD]
[TD]N/A[/TD]
[TD]5700[/TD]
[TD]13.83[/TD]
[TD][/TD]
[TD="colspan: 2"]Salary Formula[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WR30[/TD]
[TD]o[/TD]
[TD]WR[/TD]
[TD]N/A[/TD]
[TD]5600[/TD]
[TD]15.90[/TD]
[TD][/TD]
[TD="colspan: 6"]=INDEX(Price_List[Salary],MATCH(J7,Price_List[Player],0))[/TD]
[/TR]
[TR]
[TD]TE1[/TD]
[TD]o[/TD]
[TD]TE[/TD]
[TD]N/A[/TD]
[TD]6800[/TD]
[TD]15.17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TE2[/TD]
[TD]o[/TD]
[TD]TE[/TD]
[TD]N/A[/TD]
[TD]6000[/TD]
[TD]15.60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TE3[/TD]
[TD]o[/TD]
[TD]TE[/TD]
[TD]N/A[/TD]
[TD]5400[/TD]
[TD]12.07[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TE4[/TD]
[TD]o[/TD]
[TD]TE[/TD]
[TD]N/A[/TD]
[TD]5000[/TD]
[TD]13.37[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TE5[/TD]
[TD]o[/TD]
[TD]TE[/TD]
[TD]N/A[/TD]
[TD]4600[/TD]
[TD]2.27[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]TE6[/TD]
[TD]o[/TD]
[TD]TE[/TD]
[TD]N/A[/TD]
[TD]4500[/TD]
[TD]12.90[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Def1[/TD]
[TD]o[/TD]
[TD]D[/TD]
[TD]N/A[/TD]
[TD]3900[/TD]
[TD]11.07[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Def2[/TD]
[TD]o[/TD]
[TD]D[/TD]
[TD]N/A[/TD]
[TD]3800[/TD]
[TD]12.60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Def3[/TD]
[TD]o[/TD]
[TD]D[/TD]
[TD]N/A[/TD]
[TD]3700[/TD]
[TD]10.93[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Def4[/TD]
[TD]o[/TD]
[TD]D[/TD]
[TD]N/A[/TD]
[TD]3600[/TD]
[TD]9.70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Def5[/TD]
[TD]o[/TD]
[TD]D[/TD]
[TD]N/A[/TD]
[TD]3500[/TD]
[TD]10.17[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[mclary]

FYI...The correct result for the formula should be WR21, not QB20.

 

[AhoyNC]

Maybe something like this.
These are array formulas and must be entered with CTRL-SHIFT-ENTER.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L
1	Player	o	Pos	Matchup	Salary	Avg Projection
2	QB1	o	QB	N/A	7700	21.47
3	QB20	o	QB	N/A	5500	17.77			Salary Optimizer
4	QB30	o	QB	N/A	4700	16.1				Position	WR
5	RB3	o	RB	N/A	8000	19.93				Salary	$7,000
6	RB4	o	RB	N/A	7700	19.77			Rank	Name	Salary	Avg Projection
7	RB35	o	RB	N/A	4500	10.7			1	WR21	6200	17.77
8	RB36	o	RB	N/A	4500	11.8			2	WR24	5900	16.13
9	RB37	o	RB	N/A	4500	5.33			3	WR30	5600	15.9
10	RB66	o	RB	N/A	3800	2.8
11	RB67	o	RB	N/A	3800	6.37
12	WR20	o	WR	N/A	6300	15.53
13	WR21	o	WR	N/A	6200	17.77
14	WR22	o	WR	N/A	6100	13.43
15	WR23	o	WR	N/A	6000	3.5
16	WR24	o	WR	N/A	5900	16.13
17	WR29	o	WR	N/A	5700	13.83
18	WR30	o	WR	N/A	5600	15.9
19	TE1	o	TE	N/A	6800	15.17
20	TE2	o	TE	N/A	6000	15.6
21	TE3	o	TE	N/A	5400	12.07
22	TE4	o	TE	N/A	5000	13.37
23	TE5	o	TE	N/A	4600	2.27
24	TE6	o	TE	N/A	4500	12.9
25	Def1	o	D	N/A	3900	11.07
26	Def2	o	D	N/A	3800	12.6
27	Def3	o	D	N/A	3700	10.93
28	Def4	o	D	N/A	3600	9.7
29	Def5	o	D	N/A	3500	10.17
Sheet

 

[mclary]

Thanks, everything worked except the formula in K7 was missing the third "if" criteria for salary. I adjusted that and everything worked. Thanks for your help.