Excel 2007 : Using excel to return value to the left.

[LoudmouthLee]

Hi everyone,

I'm getting ready for my fantasy baseball league (it's a big one!) and I'm working on my excel spreadsheet. The spreadsheet is attached for ease...

The last tab (named: Depth) is set up to pick the highest number in a range (for instance, Catchers) and return the highest point total in that range.

So, for instance, one of my competitors is named Anthony. I set up the last tab to list who his "highest rated" catcher is by using a =MAX(Anthony!K4:K7), which returns the value. However, I need to also return the name NEXT TO that number. Any ideas?

[NBVC]

=INDEX(Anthony!J4:J7,MATCH(B4,Anthony!K4:K7,0))

but if there are duplicate max values you only get the first one....

[JBeaucaire]

Try this:

=INDEX(Anthony!$J$4:$J$7, MATCH(B4, Anthony!$K$4:$K$7, 0))



If you want to tie these formulas to the sheet matching the name you entered into A2, you could use this:

B4: =MAX(INDIRECT("'" & $A$2 & "'!K4:K7"))
B3: =INDEX(INDIRECT("'" & $A$2 & "'!J4:J7"), MATCH(B$4, INDIRECT("'" & $A$2 & "'!K4:K7"), 0))