XcelLearner
Board Regular
- Joined
- Feb 6, 2016
- Messages
- 52
- Office Version
- 365
- 2016
- Platform
- Windows
A | B | C | D | E | F | G | H | I |
1 | ||||||||
2 | Agility | Forehand | Backhand | Serve | Volley | Stamina | ||
3 | Antioxidants | Formula??? Expected Result: 5 | Formula??? Expected Result: 2 | |||||
4 | ||||||||
5 | Level | 1 | 2 | 3 | 4 | 5 | 6 | |
6 | Antioxidants | Forehand | 1 | 2 | 3 | 4 | 5 | 6 |
7 | Stamina | 6 | 5 | 4 | 3 | 2 | 1 | |
8 | ||||||||
9 | Keto Sourcing | Stamina | 4 | 4 | 4 | 4 | 4 | 4 |
10 | ||||||||
11 | Macrobiotic | Volley | 3 | 5 | 6 | 7 | 9 | 11 |
Hi, I have a Data set in table B5:I11 with what I would call 3-way data table. That problem is complicated beyond what I expected.
There are 3 items:
-Antioxidants,
-Keto Sourcing, and
-Macrobiotic.
Each item has one/or a few underlying certain metrics. Such as Antioxidants has Forehand, and Stamina. Keto Sourcing has Stamina. Macrobiotic has Volley. Each metrics has value attached to it depending on the level of the item. For example, Level 1 Antioxidants has a Forehand value of 1, and a Stamina value of 6. For my purpose, I would look for Level 5 values, which are defined in H column.
I want to build a drop down list, with chosen item in B3. For example, I choose Antioxidants, then what is the formulae in E3, and in I3 so that they can look for inputs in table B5:I11, take the name Antioxidants, then look for value of Forehand, then look for value of level 5 in cell H6 (which is 5)? Similarly, because Antioxidants has another metrics, which is Stamina, so the similar formula will be needed in I3.
When I have 2-array data table, it is quite easy. But in this so-called 3-way table, what are the formulae?
Thank you very much in advance. I hope that I made myself understood.