1

I have a complex spreadsheet with a named range (Vault) that covers chunks of data from many parts of the sheet:

Score Page

"Vault" represents a defined name and refers to the following areas on page 1 (you can see the column heading in the different areas):

'Score Page'!$C$8:$C$31
'Score Page'!$K$8:$K$20
'Score Page'!$C$36:$C$47
'Score Page'!$K$25:$K$30
'Score Page'!$K$35:$K$41
'Score Page'!$C$52:$C$57
'Score Page'!$K$47:$K$57

(In the image above, the ranges below row 35 are out of view)

On this sheet, I Have the athlete name, school and the scores received for the 4 events for all participating. I use the LARGE function to pull the top score for the Vault event:

=Large(vault,1)

This gets placed on a result page (separate Sheet from Athlete info):

Results

I need a formula that will tell me the cell address for where the LARGE function pulled the number. There could be ties (and usually are ties) so MAX is out. I have no experience with VBA so I really need to do this using formulas.

I can get the top score with the "Large" function but can't seem to figure out the formula to pull the name and school to go with that score on the result page. Right now I do this by physically looking at the raw data page to find the score I get on the result page and typing in the information.

I do have a column with a formula also that will put the place of that score (1st, 2nd 3rd etc) and account for ties so I could have 1st, 2nd, 2nd, third, etc.

On the results page, the columns are Place (under the event label), School, Athlete Name, and Score. So far I can get Place and Score to happen.

My thought is if I can get the cell address for where the value of the formula LARGE(vault,1) is pulling its answer I can use that cell address to get the other addresses to pull the school and kids name. Under typical circumstances, I could lookup the result in the data to find its location using a function like VLOOKUP or MATCH. However, the fact that the named range covers many separate areas on the sheet complicates that kind of approach.

To recap, the problem is to find the cell that the LARGE function found its result in.

Improve this question
13
  • Use INDEX plus MATCH. MATCH will find the score in the column from which it was chosen. Index will then use that position to select the value from another column for the same row. Some help: contextures.com/xlFunctions03.html. If the scores were in the first column, you could use VLOOKUP. INDEX+MATCH doesn't have that restriction. If you have trouble figuring it out, post a mockup of your data in your answer and someone can base an answer on that. Another link to Microsoft help: support.microsoft.com/kb/214142
    – fixer1234
    Dec 27, 2014 at 0:56
  • Vlookup won't work as there are multiple columns that the info comes from. I will investigate the Index plus match options.
    – Cheryl
    Dec 27, 2014 at 2:01
  • If you can't figure it out, add a mockup of your data to your question. MATCH will have the same issue if the lookup can reference different columns. (VLOOKUP has the additional problem that the lookup must be done in the left-most column).
    – fixer1234
    Dec 27, 2014 at 2:07
  • the formula I am currently using on the results page is "Large(vault,1)" where "Vault" represents several areas on the raw data page for the vault scores not all in the same column. What I want to do is have a formula that basically goes along the line of - I want the cell address where the value of Large(vault,1) was found. I can't seem to figure out how to get the mock up of my data to show here.
    – Cheryl
    Dec 27, 2014 at 2:07
  • It takes less than a minute to create a Dropbox account. You may already have a OneDrive account. Or a Google account. All these can be used to share files. Without a data sample I cannot picture how ' "Vault" represents several areas on the raw data page for the vault scores not all in the same column.' If you want to reverse the formula that uses "Vault" then we need to know what "Vault" consists of. Better than a screenshot would be a sample file. Again, Dropbox, OneDrive, Google.
    – teylyn
    Dec 27, 2014 at 7:16

1 Answer 1

Reset to default
1

=CELL("address",Large(vault,1))

Improve this answer
1
  • 2
    Welcome to Super User. Although this may help to solve the problem, it doesn't explain why and/or how it addresses the issue. Providing this additional context would significantly improve its long-term educational value. Please edit your answer to add explanation, including what limitations and assumptions apply. Thanks.
    – fixer1234
    Sep 6, 2016 at 21:47

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .