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?

Player

o

Pos

Matchup

Salary

Avg Projection

QB1

o

QB

N/A

7700

21.47

QB20

o

QB

N/A

5500

17.77

Salary Optimizer

QB30

o

QB

N/A

4700

16.10

Position

WR

RB3

o

RB

N/A

8000

19.93

Salary

$7,000

RB4

o

RB

N/A

7700

19.77

Rank

Name

Salary

Avg Projection

RB35

o

RB

N/A

4500

10.70

1

QB20

$5,500

17.8

RB36

o

RB

N/A

4500

11.80

2

WR24

$5,900

16.1

RB37

o

RB

N/A

4500

5.33

3

WR30

$5,600

15.9

RB66

o

RB

N/A

3800

2.80

RB67

o

RB

N/A

3800

6.37

Projection Formula

WR20

o

WR

N/A

6300

15.53

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

WR21

o

WR

N/A

6200

17.77

WR22

o

WR

N/A

6100

13.43

Name Formula

WR23

o

WR

N/A

6000

3.50

=INDEX(Price_List[Player],MATCH(L7,Price_List[Avg Projection],0))

WR24

o

WR

N/A

5900

16.13

WR29

o

WR

N/A

5700

13.83

Salary Formula

WR30

o

WR

N/A

5600

15.90

=INDEX(Price_List[Salary],MATCH(J7,Price_List[Player],0))

TE1

o

TE

N/A

6800

15.17

TE2

o

TE

N/A

6000

15.60

TE3

o

TE

N/A

5400

12.07

TE4

o

TE

N/A

5000

13.37

TE5

o

TE

N/A

4600

2.27

TE6

o

TE

N/A

4500

12.90

Def1

o

D

N/A

3900

11.07

Def2

o

D

N/A

3800

12.60

Def3

o

D

N/A

3700

10.93

Def4

o

D

N/A

3600

9.70

Def5

o

D

N/A

3500

10.17

<colgroup><col><col><col><col><col><col><col span="7"></colgroup><tbody>
</tbody>

 

[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.