INDEX using INDIRECT with OR function

[brooksc29]

I am trying to use the INDEX function to refer to a range on another sheet and look for either of two criteria for a result.

This is my current formula and I'm off, just can't figure out where.

=INDEX(INDIRECT(C27),MATCH(OR("RHP-S","LHP-S"),INDEX(INDIRECT(C27),0,2),1))

Feel like I should be able to get this but I'm stuck.

Any help?

 

[brooksc29]

also, do you think there is any way to set a pre condition on the sheet that there cannot be any duplicate names returned, and if the formula results in a name that already appears on the sheet, automatically return the next occurrence of the criteria?

 

[Domenic]

Can you provide a small sample of the data (10, 15 or maybe 20 rows), along with the actual results that you expect?

 

[brooksc29]

-- removed inline image ---

 

[brooksc29]

TEAM

YEAR

TOTAL

NYM

2014

$ 73,775,000

LF

Granderson, Curtis

$ 13,000,000

CF

Lagares, Juan

$ 500,000

RF

Young, Chris

$ 7,250,000

OF

Lagares, Juan

$ 500,000

SS

Tejada, Ruben

$ 1,100,000

2B

Murphy, Daniel

$ 5,700,000

SS

Tovar, Wilfredo

$ 500,000

2B

-

3B

Wright, David

$ 20,000,000

1B

Davis, Ike

$ 3,500,000

3B

Lutz, Zach

$ 500,000

1B

-

SS

-

SP

Colon, Bartolo

$ 9,000,000

CL

Parnell, Bobby

$ 3,700,000

SS

-

SP

Niese, Jon

$ 5,050,000

RP

Edgin, Josh

$ 500,000

3B

Lutz, Zach

$ 500,000

SP

Gee, Dillon

$ 3,625,000

RP

Familia, Jeurys

$ 500,000

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup>

 

[brooksc29]

MLB New York NL 14 : MLB New York NL 14.xls
.	NEW YORK NL		MLS thru '13 season		Updated 1/14	salary + p-r bonus
.		Pos'n	ML Srv	Agent	Length / Total Value	2014
.	TOTAL - NYM					$73,775,000
.	Wright, David	3b	9.075	Levinsons ACES	8 yr/$138M (13-20)	$20,000,000
.	Granderson, Curtis	lf-cf	8.077	Matt Brown	4 yr/$60M (14-17)	$13,000,000
.	Colon, Bartolo	rhp-s	15.061	Adam Katz WMG (prev Mitch Frankel)	2 yr/$20M (14-15)	$9,000,000
.	Young, Chris	rf	7.045	CAA (Jeff Berry)	1 yr/$7.25M (14)	$7,250,000
.	Murphy, Daniel	2b	4.109	Levinsons ACES	1 yr/$5.7M (14)	$5,700,000
.	Niese, Jon	lhp-s	4.107	O'Connell Sports	5 yr/$25.5195M (12-16)+opts	$5,050,000
.	Parnell, Bobby	rhp-c	4.132	Levinsons ACES	1 yr/$3.7M (14)	$3,700,000
.	Gee, Dillon	rhp-s	3.028	Select Sports	1 yr/$3.625M (14)	$3,625,000
.	Davis, Ike	1b	3.155	Octagon	1 yr/$3.5M (14)	$3,500,000
.	Young Jr., Eric	lf	3.123	Dan Lozano	1 yr/$1.85M (14)	$1,850,000
.	Tejada, Ruben	ss	2.171	Peter Greenberg	1 yr/$1.1M (14)	$1,100,000

<tbody>
</tbody><colgroup><col><col><col><col><col><col><col></colgroup>

 

[brooksc29]

The first pic is the sample MLCLUBS sheet that will pull the data from the second sheet.


The second pic is the data set for NYM. There are 30 different data sets, all of different sheets. The Drop down box in A2 on MLCLUBS chooses the range. The data is all sorted by first by col G (salary) on each individual sheet, and then sorted again by col D (ML Srv). There is a macro that runs that auto sorts the data.


I would like to be able to create a sort of "depth chart" that will not have duplicates on the page. I need to be able to look for the first examples of certain data in Col C on each individual data page.