Lookup Name Based on Highest Value within Named Ranges

I've hit another brick wall.

I need a formula that will give me a persons name that is stored in Column B based on the highest value within 5 named ranges. Each named range is a selection of every other cell in individual columns i.e. named range junior1 is cells in column E, named range junior2 is cells within column H and so on.

I've attached the worksheet as it as present.Sample Running Scores 3.xlsx

Cell Y3 shows the highest individual score shot (not handicap score) from columns E,H,K,N,Q (or more accurately the named ranges Junior1, Junior2, Junior3, Junior4, Junior5 within these columns). Each of these columns represents a different round in each league, in this case a Junior National League. I now need Y4 to show me the name of the person that shot the highest individual score, in this case Person 21.

I know I need to use a combination of Index & Max in some way but I can't work out what or how.

=INDEX(B:B,MAX(IF(E3:Q106=Y3,ROW(A3:A106))))

entered as an array formula (confirm with ctrl+shift+enter) would work, provided that the value in Y3 only occurs once in the rest of the table.

"junior1" isn't the same size as U3:U106 so overfow errors are a problem; I think you're going to get incorrect indexing as well. "junior1" is also non-contiguous which wil probably be problematic but I didn't test that.

The reason that Find Names!B4 is delivering #N/A seems to be that the MAX(junior1) doesn't match anything in range Scores!E3:E106, so the MATCH is throwing an error.

Using merged cells inside a dataset (Scores!B1:B100 or so) is a bad idea.

Actually, related to that, this goofy split-level setup with Score/Handicap score alternating down the rows is tripling the complexity of everything you do; can you set this table up a little more properly? Move all the data for one person into one row.

As it stands, you're borrowing a lot of trouble.

I think your best bet is setting up a helper table that finds the max score for each range, then picks the highest of those to pump through an INDEX? Well, the logic is one thing, but getting it to deal with these non-contiguous ranges and merged outputs will require a lot of finagling, I think you'd be better off fixing your table first.

Originally Posted by XOR LX

Hi,

Can you just clarify why the ranges for junior1, junior2 and junior3 extend to row 85, though those for junior4 and junior5 to only row 75?

Regards

Ahh! It's meant to go down to row 105 for some reason that I don't understand the ranges keep loosing rows????

Originally Posted by ben_hensel

"junior1" isn't the same size as U3:U106 so overfow errors are a problem; I think you're going to get incorrect indexing as well. "junior1" is also non-contiguous which wil probably be problematic but I didn't test that.

The reason that Find Names!B4 is delivering #N/A seems to be that the MAX(junior1) doesn't match anything in range Scores!E3:E106, so the MATCH is throwing an error.

Using merged cells inside a dataset (Scores!B1:B100 or so) is a bad idea.

Actually, related to that, this goofy split-level setup with Score/Handicap score alternating down the rows is tripling the complexity of everything you do; can you set this table up a little more properly? Move all the data for one person into one row.

As it stands, you're borrowing a lot of trouble.

I think your best bet is setting up a helper table that finds the max score for each range, then picks the highest of those to pump through an INDEX? Well, the logic is one thing, but getting it to deal with these non-contiguous ranges and merged outputs will require a lot of finagling, I think you'd be better off fixing your table first.

Yeah I hear what you are saying. It's difficult because the whole point of this worksheet is to work out scores and winners which are worked out in different ways. For example there is a:
Grand Winner based on all round over all leagues
League Winner based on all round of each individual league
Round Winners for each league
Highest Score winners based on each league across all rounds
Most improved handicap rating

All of the winners are worked out in different ways, some are based on handicap scores, some based on scores shot, some based on the sum of the highest 3 scores some on individual scores. To say it's complex is an understatement, I've been working on this for 4 days now!

The reason I put handicap and scores shot in the same column is that the size of the worksheet is already very large and having two columns for every round of every league is just going to double it but perhaps that's the only way.

Originally Posted by ben_hensel

"junior1" isn't the same size as U3:U106 so overfow errors are a problem; I think you're going to get incorrect indexing as well. "junior1" is also non-contiguous which wil probably be problematic but I didn't test that.

The reason that Find Names!B4 is delivering #N/A seems to be that the MAX(junior1) doesn't match anything in range Scores!E3:E106, so the MATCH is throwing an error.

Using merged cells inside a dataset (Scores!B1:B100 or so) is a bad idea.

Actually, related to that, this goofy split-level setup with Score/Handicap score alternating down the rows is tripling the complexity of everything you do; can you set this table up a little more properly? Move all the data for one person into one row.

As it stands, you're borrowing a lot of trouble.

I think your best bet is setting up a helper table that finds the max score for each range, then picks the highest of those to pump through an INDEX? Well, the logic is one thing, but getting it to deal with these non-contiguous ranges and merged outputs will require a lot of finagling, I think you'd be better off fixing your table first.

Yeah I hear what you are saying. It's difficult because the whole point of this worksheet is to work out scores and winners which are worked out in different ways. For example there is a:
Grand Winner based on all round over all leagues
League Winner based on all round of each individual league
Round Winners for each league
Highest Score winners based on each league across all rounds
Most improved handicap rating

All of the winners are worked out in different ways, some are based on handicap scores, some based on scores shot, some based on the sum of the highest 3 scores some on individual scores. To say it's complex is an understatement, I've been working on this for 4 days now!

The reason I put handicap and scores shot in the same column is that the size of the worksheet is already very large and having two columns for every round of every league is just going to double it but perhaps that's the only way.

Okay I've reworked the sheet so that now everything persons details is in 1 row (I may later move each round to a separate worksheet and have a separate worksheet for the Results).Sample Running Scores 4.xlsx

Having contiguous cells has rectified the problem I was having with named ranges however I don't know Excel well enough though to work out how to capture the name of the person who shot the highest score in the Junior National League (the highest score in this league in all 5 rounds) and display it in cell AS4

To my simple brain the formula I need is:

Formula:

Please Login or Register  to view this content.

Or

Formula:

Please Login or Register  to view this content.

But when I try this Excel tells me that I'm entering too many arguments for this function. I've tried explaining to Excel that I'm entering into too many arguments in general with Excel itself but this hasn't helped