Jul 13 2021 01:49 PM
Jul 13 2021 01:49 PM
I have a data table full of information on certain people. I would like to be able to look up the date of of a name and a certain lifts. For example
I want to be able to look up athletes A bench. So I would have athlete A in the drop down list, and bench, then it provide the bench for athlete A.
Any help would be great.
Jul 14 2021 01:50 AM - edited Jul 14 2021 02:09 AMSolution
=SUMPRODUCT( (NUMBERS[[#Headers];[BENCH]:[PULL UPS]] = A8) * (NUMBERS[NAME] = A7) * NUMBERS[[BENCH]:[PULL UPS]] )
If you only have 1 row of data per [NAME] in table NUMBERS the followings could do it as well
=INDEX(NUMBERS[[BENCH]:[PULL UPS]], MATCH(A7,NUMBERS[NAME],0), MATCH(A8,NUMBERS[[#Headers],[BENCH]:[PULL UPS]],0) )
in A12 (Excel 365/Web only)
=FILTER( FILTER(NUMBERS, NUMBERS[NAME] = A7), NUMBERS[#Headers] = A8 )
Your updated file is attached
EDIT: To be complete. If Excel 365/Web and more than 1 row of data per [NAME] in table NUMBERS:
FILTER(NUMBERS, NUMBERS[NAME] = A7),
NUMBERS[#Headers] = A8
Jul 14 2021 03:30 AM
Interesting use of FILTER. I don't think I have ever used nested row and column filters. I can see circumstances where it could be useful (e.g. return the average of the athletes top 3 performances).
To return a single value in 365, I would tend to go for nested XLOOKUPs.
= LET( athleteData, XLOOKUP(AthleteName, NUMBERS[NAME], NUMBERS), XLOOKUP(Lift, NUMBERS[#Headers], athleteData))
or, here, use LET to lay out the formula steps sequentially.
Jul 14 2021 05:15 AM
Jul 14 2021 06:37 AM
One more variant for the collection
- Create on NUMBERS PivotTable in new sheet adding data to data models
- Name to rows, Sum of each column to values
- add formula
=CUBEVALUE("ThisWorkbookDataModel", CUBEMEMBER("ThisWorkbookDataModel","[Measures].[Sum of " & $A$8 & "]"), CUBEMEMBER("ThisWorkbookDataModel","[NUMBERS].[NAME].[" & $A$7 & "]"))
- delete new sheet with PivotTable
Jul 17 2021 05:47 AM
Jul 17 2021 06:09 AM - edited Jul 17 2021 09:24 AM
Difficult to say. You've got n proposals and we have no idea which one you implemented
Could you upload a sample with what you implemented + give a couple of examples of what you now expect?