Dear Excel experts, I've lost count of the number of Mike Girvin and Bill Jelen videos I've watched trying to sort this myself, but I'm beginning to think I might be here forever, so it's time to ask for help.
I've got a Workbook with 25 or 30 or so years of sports data, with each individual year on its own sheet. They're named "2020", "2019" etc, down to "1993", although I will add more years.
Then on the first page I have a summary sheet in which I am trying to gather all the relevant data together.
What I am trying to do, in a nutshell, is two things:
(i) Look across 25 years of data and list the highest values (I'm currently starting with the top 30 values, but this will likely expand to the top 250) in a specific category (column K on each sheet) in descending order and return the worksheet (year) from where my search has found its result. My main problem with this part is that there are duplicate values (more on that in a moment).
(ii) I then want to use this value to return a raft of other values from the same row on the sheet where the result was found. E.g. Player name, position they play, NFL team they play for, round and pick number of the NFL draft in which they were taken. My main problem with this part, if we can get over the duplicates problem above, is that most of the complementary data I wish to return is to the left of the column where my initial data is. Rightly or wrongly, I've got the notion that VLOOKUP won't do left look-ups and that INDEX MATCH won't work over multiple sheets.
I'm not ashamed to say that I am punching above my weight here, I didn't know what a 3D formula was before weeks of Googling, reading and watching Excel YouTube tips. I only have Excel 2007 and I haven't mastered pivot tables or VBA.
So, small talk over, here's where I'm at:
On my master sheet I have:
In column AE18-AE47 - a list of ascending numbers from 1-30.
In column AF18-AF47 - a formula which searches years 2020-1993 on other sheets and successfully returns the highest values, in ascending order from those sheets in column K.
This is the formula: =LARGE('2020:1993'!$K$3:$K$260,$AE18)
In column AH18-AH45 - a list of the years 2020-1993 in descending order which are to be used in a formula in column AG. Yes, this should be a named range, but I've only just discovered those too (insert embarrassed face emoji!).
In column AG18-AG47 = a formula which takes the highest values returned in column AF and tells me which year (worksheet) it found the value:
This is the formula: INDEX($AH$18:$AH$45,MATCH(1,COUNTIF(INDIRECT("'"&$AH$18:$AH$45&"'!$K$3:$K$260"),$AF18),0))
All would be rosy, and this works fine. Unless there are duplicate high values, of which there are many. For instance there are 3 values of 118 at rank 18, 19 and 20 and 3 values of 117 at rank 21, 22, 23. The formula above only looks as far as the first instance it finds and returns its location for this and subsequent duplicates, or "ties", it will not tell me the sheet were the second and third equal values are located. An additional headache on top of this is I know of at least a couple of occurrences where there are duplicate values not only on different sheets, but also on the SAME SHEET. "MATCH(1," etc in the formula above throws out a N/A error in this instance, because here it's obviously MATCH(2, or MATCH(3.
Please, has anyone any ideas? I've experimented without any progress and not having a clue as to how to approach it really means this is all blind guesswork on my part. I'll bite your hand off at helping sort step one (this) for now and if successful perhaps we could revisit the "left lookups" from the returned data in due course. Screen grab attached with duplicate problem highlighted; many thanks for your time.
I've got a Workbook with 25 or 30 or so years of sports data, with each individual year on its own sheet. They're named "2020", "2019" etc, down to "1993", although I will add more years.
Then on the first page I have a summary sheet in which I am trying to gather all the relevant data together.
What I am trying to do, in a nutshell, is two things:
(i) Look across 25 years of data and list the highest values (I'm currently starting with the top 30 values, but this will likely expand to the top 250) in a specific category (column K on each sheet) in descending order and return the worksheet (year) from where my search has found its result. My main problem with this part is that there are duplicate values (more on that in a moment).
(ii) I then want to use this value to return a raft of other values from the same row on the sheet where the result was found. E.g. Player name, position they play, NFL team they play for, round and pick number of the NFL draft in which they were taken. My main problem with this part, if we can get over the duplicates problem above, is that most of the complementary data I wish to return is to the left of the column where my initial data is. Rightly or wrongly, I've got the notion that VLOOKUP won't do left look-ups and that INDEX MATCH won't work over multiple sheets.
I'm not ashamed to say that I am punching above my weight here, I didn't know what a 3D formula was before weeks of Googling, reading and watching Excel YouTube tips. I only have Excel 2007 and I haven't mastered pivot tables or VBA.
So, small talk over, here's where I'm at:
On my master sheet I have:
In column AE18-AE47 - a list of ascending numbers from 1-30.
In column AF18-AF47 - a formula which searches years 2020-1993 on other sheets and successfully returns the highest values, in ascending order from those sheets in column K.
This is the formula: =LARGE('2020:1993'!$K$3:$K$260,$AE18)
In column AH18-AH45 - a list of the years 2020-1993 in descending order which are to be used in a formula in column AG. Yes, this should be a named range, but I've only just discovered those too (insert embarrassed face emoji!).
In column AG18-AG47 = a formula which takes the highest values returned in column AF and tells me which year (worksheet) it found the value:
This is the formula: INDEX($AH$18:$AH$45,MATCH(1,COUNTIF(INDIRECT("'"&$AH$18:$AH$45&"'!$K$3:$K$260"),$AF18),0))
All would be rosy, and this works fine. Unless there are duplicate high values, of which there are many. For instance there are 3 values of 118 at rank 18, 19 and 20 and 3 values of 117 at rank 21, 22, 23. The formula above only looks as far as the first instance it finds and returns its location for this and subsequent duplicates, or "ties", it will not tell me the sheet were the second and third equal values are located. An additional headache on top of this is I know of at least a couple of occurrences where there are duplicate values not only on different sheets, but also on the SAME SHEET. "MATCH(1," etc in the formula above throws out a N/A error in this instance, because here it's obviously MATCH(2, or MATCH(3.
Please, has anyone any ideas? I've experimented without any progress and not having a clue as to how to approach it really means this is all blind guesswork on my part. I'll bite your hand off at helping sort step one (this) for now and if successful perhaps we could revisit the "left lookups" from the returned data in due course. Screen grab attached with duplicate problem highlighted; many thanks for your time.
