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.

  • 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 '14 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 '14 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 '14 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 '14 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 '14 at 7:16
1

=CELL("address",Large(vault,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 '16 at 21:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.