Lookup in a (so-called) 3-way data table

[XcelLearner]

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.

 

[ExcelGzh]

XcelLearner

The required formula for cell E3 looks like this

INDEX($H$5:$H$12,MATCH(E$2,INDEX($C$5:$C$12,MATCH($B3,$B$5:$B$12,0),1):INDEX($C$12,1,1),0)+MATCH($B3,$B$5:$B$12,0)-1,1)

This bit INDEX($C$5:$C$12,MATCH($B3,$B$5:$B$12,0),1):INDEX($C$12,1,1),0)+MATCH($B3,$B$5:$B$12,0)-1 creates a range that MATCH(E$2 looks in which generates a row number for INDEX($H$5:$H$12 to use.

If you copy the formula into cell I3 then E$2 becomes I$2 but the rest stays the same.

 

[XcelLearner]

XcelLearner

The required formula for cell E3 looks like this

INDEX($H$5:$H$12,MATCH(E$2,INDEX($C$5:$C$12,MATCH($B3,$B$5:$B$12,0),1):INDEX($C$12,1,1),0)+MATCH($B3,$B$5:$B$12,0)-1,1)

This bit INDEX($C$5:$C$12,MATCH($B3,$B$5:$B$12,0),1):INDEX($C$12,1,1),0)+MATCH($B3,$B$5:$B$12,0)-1 creates a range that MATCH(E$2 looks in which generates a row number for INDEX($H$5:$H$12 to use.

If you copy the formula into cell I3 then E$2 becomes I$2 but the rest stays the same.

Thank you very much for your help. I am applying the formula into the data set I have. May I have one question? The data table above only has row 11, so do we need row 12 in our formula (such as C5:C12 rather than C5:C11)? And the component INDEX($C$12,1,1) seems to refer to an empty cell?

Thanks again for your time.

 

[Eric W]

You could try something like this:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I
1
2	Level:	5		Agility	Forehand	Backhand	Serve	Volley	Stamina
3	Item:	Antioxidants			5				2
4
5		Level		1	2	3	4	5	6
6		Antioxidants	Forehand	1	2	3	4	5	6
7		Antioxidants	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
Sheet8

Cell Formulas
Range	Formula
D3:I3	D3	=IFERROR(1/(1/(SUMPRODUCT($D$6:$I$11*($B$6:$B$11=$B$3)*($C$6:$C$11=D$2)*($D$5:$I$5=$B$2)))),"")

Note that this requires you to put the item name in each row of column B, you see I added "Antioxidants" to B7. I also added the B2 cell to choose the column.

 

[ExcelGzh]

XcelLearner

Yes, you're right. You can change 12 into 11 and it would all still work. You could also use this revised formula and it would work no matter how many rows you had.

=INDEX($H:$H,MATCH(I$2,INDEX($C:$C,MATCH($B3,$B:$B,0),1):INDEX($C:$C,MATCH(MAX($A:$A),$A:$A,0),1),0)+MATCH($B3,$B:$B,0)-1,1)

 

[XcelLearner]

Hi,

You could try something like this:

Book1 (version 1).xlsb
	A	B	C	D	E	F	G	H	I
1
2	Level:	5		Agility	Forehand	Backhand	Serve	Volley	Stamina
3	Item:	Antioxidants			5				2
4
5		Level		1	2	3	4	5	6
6		Antioxidants	Forehand	1	2	3	4	5	6
7		Antioxidants	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
Sheet8

Cell Formulas
Range	Formula
D3:I3	D3	=IFERROR(1/(1/(SUMPRODUCT($D$6:$I$11*($B$6:$B$11=$B$3)*($C$6:$C$11=D$2)*($D$5:$I$5=$B$2)))),"")

Note that this requires you to put the item name in each row of column B, you see I added "Antioxidants" to B7. I also added the B2 cell to choose the column.

Hi Eric,

Thank you very much for your solution. It was an elegant one, very easy to use, and to adapt with real data set. Your usage of SUMPRODUCT also amazes me, I was almost speechless that SUMPRODUCT can work in that way :|

In your formula:
SUMPRODUCT($D$6:$I$11*($B$6:$B$11=$B$3)*($C$6:$C$11=D$2)*($D$5:$I$5=$B$2))
I can barely understand the logic of looking for B3, and D2, and B2 in their respective array, but I don't understand the component of $D$6:$I$11 in a multiplier with the rest. Can you please elaborate on that?
And why 1/1//SUMPRODUCT please?

Again, thanks a lot for a perfect solution that works magically.
Best,
Nam

 

[Eric W]

SUMPRODUCT is an enormously powerful function, and the standard documentation barely touches on what it can do. I couldn't find my usual link, so here's a brief explanation of how it works in this case:

Book3
	A	B	C	D	E	F	G	H	I
1
2	Level:	5		Agility	Forehand	Backhand	Serve	Volley	Stamina
3	Item:	Antioxidants			5				2
4
5		Level		1	2	3	4	5	6
6		Antioxidants	Forehand	1	2	3	4	5	6
7		Antioxidants	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
Sheet1

Cell Formulas
Range	Formula
D3:I3	D3	=IFERROR(1/(1/(SUMPRODUCT($D$6:$I$11*($B$6:$B$11=$B$3)*($C$6:$C$11=D$2)*($D$5:$I$5=$B$2)))),"")

The D5:I11 grid is outlined. It forms a 2-dimensional array. This is all calculated internally, but I'll show how it works on the sheet. The next parameter in the formula is (B6:B11=B3), or (B6:B11="Antioxidants"). This results in a TRUE or FALSE, like this:

Book3
	A	B	C	D	E	F	G	H	I
1
2	Level:	5		Agility	Forehand	Backhand	Serve	Volley	Stamina
3	Item:	Antioxidants			5				2
4
5		Level		1	2	3	4	5	6
6		TRUE	Forehand	1	2	3	4	5	6
7		TRUE	Stamina	6	5	4	3	2	1
8		FALSE
9		FALSE	Stamina	4	4	4	4	4	4
10		FALSE
11		FALSE	Volley	3	5	6	7	9	11
Sheet1

When Excel uses a TRUE/FALSE value in an equation, TRUE = 1, and FALSE = 0. Also, when Excel multiplies a 1-D array (B6:B11) times a 2-D array (D6:I11), it multiplies the row element in the 1-D array times each column element in the 2-D array in the matching row, so something like this:

Book3
	A	B	C	D	E	F	G	H	I
1
2	Level:	5		Agility	Forehand	Backhand	Serve	Volley	Stamina
3	Item:	Antioxidants			5				2
4
5		Level		1	2	3	4	5	6
6		1	Forehand	1	2	3	4	5	6
7		1	Stamina	6	5	4	3	2	1
8		0
9		0	Stamina	4	4	4	4	4	4
10		0
11		0	Volley	3	5	6	7	9	11
Sheet1

resulting in:

Book3
	A	B	C	D	E	F	G	H	I
1
2	Level:	5		Agility	Forehand	Backhand	Serve	Volley	Stamina
3	Item:	Antioxidants			5				2
4
5		Level		1	2	3	4	5	6
6		1	Forehand	1	2	3	4	5	6
7		1	Stamina	6	5	4	3	2	1
8		0		0	0	0	0	0	0
9		0	Stamina	0	0	0	0	0	0
10		0		0	0	0	0	0	0
11		0	Volley	0	0	0	0	0	0
Sheet1

The next parameter is (C6:C11=D$2). If we look at the E3 formula, this equates to (C6:C11="Forehand"). And by the exact same process we get:

Book3
	A	B	C	D	E	F	G	H	I
1
2	Level:	5		Agility	Forehand	Backhand	Serve	Volley	Stamina
3	Item:	Antioxidants			5				2
4
5		Level		1	2	3	4	5	6
6		1	1	1	2	3	4	5	6
7		1	0	0	0	0	0	0	0
8		0	0	0	0	0	0	0	0
9		0	0	0	0	0	0	0	0
10		0	0	0	0	0	0	0	0
11		0	0	0	0	0	0	0	0
Sheet1

The last parameter is (D5:I5=B2). This is a horizontal 1-D array instead of a vertical array, so it multiplies each row by the corresponding column value:

Book3
	A	B	C	D	E	F	G	H	I
1
2	Level:	5		Agility	Forehand	Backhand	Serve	Volley	Stamina
3	Item:	Antioxidants			5				2
4
5		Level		FALSE	FALSE	FALSE	FALSE	TRUE	TRUE
6		1	1	1	2	3	4	5	6
7		1	0	0	0	0	0	0	0
8		0	0	0	0	0	0	0	0
9		0	0	0	0	0	0	0	0
10		0	0	0	0	0	0	0	0
11		0	0	0	0	0	0	0	0
Sheet1

Resulting in:

Book3
	A	B	C	D	E	F	G	H	I
1
2	Level:	5		Agility	Forehand	Backhand	Serve	Volley	Stamina
3	Item:	Antioxidants			5				2
4
5		Level		FALSE	FALSE	FALSE	FALSE	TRUE	TRUE
6		1	1	0	0	0	0	5	0
7		1	0	0	0	0	0	0	0
8		0	0	0	0	0	0	0	0
9		0	0	0	0	0	0	0	0
10		0	0	0	0	0	0	0	0
11		0	0	0	0	0	0	0	0
Sheet1

Finally, the SUM part of SUMPRODUCT kicks in and adds everything in the array together. Since everything is 0 except for the H6 cell, that's the answer. Google SUMPRODUCT, there are a lot of sites that give more explanation and examples.

As far as the 1/(1/ trick, that's used to show a blank cell instead of a 0. Here's a link to an article I wrote on how that works (option 4), as well as some other ways to do it that might be better for you.

Suppressing zeros

A frequent question on the forums is "how do I not display a zero?" There are several methods to do so, each with their own pluses and minuses. I will explain the various methods here as a guide, and the interested user can choose the one most...

www.mrexcel.com

Good luck!

 

[XcelLearner]

SUMPRODUCT is an enormously powerful function, and the standard documentation barely touches on what it can do. I couldn't find my usual link, so here's a brief explanation of how it works in this case:

Book3
	A	B	C	D	E	F	G	H	I
1
2	Level:	5		Agility	Forehand	Backhand	Serve	Volley	Stamina
3	Item:	Antioxidants			5				2
4
5		Level		1	2	3	4	5	6
6		Antioxidants	Forehand	1	2	3	4	5	6
7		Antioxidants	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
Sheet1

Cell Formulas
Range	Formula
D3:I3	D3	=IFERROR(1/(1/(SUMPRODUCT($D$6:$I$11*($B$6:$B$11=$B$3)*($C$6:$C$11=D$2)*($D$5:$I$5=$B$2)))),"")

The D5:I11 grid is outlined. It forms a 2-dimensional array. This is all calculated internally, but I'll show how it works on the sheet. The next parameter in the formula is (B6:B11=B3), or (B6:B11="Antioxidants"). This results in a TRUE or FALSE, like this:

Book3
	A	B	C	D	E	F	G	H	I
1
2	Level:	5		Agility	Forehand	Backhand	Serve	Volley	Stamina
3	Item:	Antioxidants			5				2
4
5		Level		1	2	3	4	5	6
6		TRUE	Forehand	1	2	3	4	5	6
7		TRUE	Stamina	6	5	4	3	2	1
8		FALSE
9		FALSE	Stamina	4	4	4	4	4	4
10		FALSE
11		FALSE	Volley	3	5	6	7	9	11
Sheet1

When Excel uses a TRUE/FALSE value in an equation, TRUE = 1, and FALSE = 0. Also, when Excel multiplies a 1-D array (B6:B11) times a 2-D array (D6:I11), it multiplies the row element in the 1-D array times each column element in the 2-D array in the matching row, so something like this:

Book3
	A	B	C	D	E	F	G	H	I
1
2	Level:	5		Agility	Forehand	Backhand	Serve	Volley	Stamina
3	Item:	Antioxidants			5				2
4
5		Level		1	2	3	4	5	6
6		1	Forehand	1	2	3	4	5	6
7		1	Stamina	6	5	4	3	2	1
8		0
9		0	Stamina	4	4	4	4	4	4
10		0
11		0	Volley	3	5	6	7	9	11
Sheet1

resulting in:

Book3
	A	B	C	D	E	F	G	H	I
1
2	Level:	5		Agility	Forehand	Backhand	Serve	Volley	Stamina
3	Item:	Antioxidants			5				2
4
5		Level		1	2	3	4	5	6
6		1	Forehand	1	2	3	4	5	6
7		1	Stamina	6	5	4	3	2	1
8		0		0	0	0	0	0	0
9		0	Stamina	0	0	0	0	0	0
10		0		0	0	0	0	0	0
11		0	Volley	0	0	0	0	0	0
Sheet1

The next parameter is (C6:C11=D$2). If we look at the E3 formula, this equates to (C6:C11="Forehand"). And by the exact same process we get:

Book3
	A	B	C	D	E	F	G	H	I
1
2	Level:	5		Agility	Forehand	Backhand	Serve	Volley	Stamina
3	Item:	Antioxidants			5				2
4
5		Level		1	2	3	4	5	6
6		1	1	1	2	3	4	5	6
7		1	0	0	0	0	0	0	0
8		0	0	0	0	0	0	0	0
9		0	0	0	0	0	0	0	0
10		0	0	0	0	0	0	0	0
11		0	0	0	0	0	0	0	0
Sheet1

The last parameter is (D5:I5=B2). This is a horizontal 1-D array instead of a vertical array, so it multiplies each row by the corresponding column value:

Book3
	A	B	C	D	E	F	G	H	I
1
2	Level:	5		Agility	Forehand	Backhand	Serve	Volley	Stamina
3	Item:	Antioxidants			5				2
4
5		Level		FALSE	FALSE	FALSE	FALSE	TRUE	TRUE
6		1	1	1	2	3	4	5	6
7		1	0	0	0	0	0	0	0
8		0	0	0	0	0	0	0	0
9		0	0	0	0	0	0	0	0
10		0	0	0	0	0	0	0	0
11		0	0	0	0	0	0	0	0
Sheet1

Resulting in:

Book3
	A	B	C	D	E	F	G	H	I
1
2	Level:	5		Agility	Forehand	Backhand	Serve	Volley	Stamina
3	Item:	Antioxidants			5				2
4
5		Level		FALSE	FALSE	FALSE	FALSE	TRUE	TRUE
6		1	1	0	0	0	0	5	0
7		1	0	0	0	0	0	0	0
8		0	0	0	0	0	0	0	0
9		0	0	0	0	0	0	0	0
10		0	0	0	0	0	0	0	0
11		0	0	0	0	0	0	0	0
Sheet1

Finally, the SUM part of SUMPRODUCT kicks in and adds everything in the array together. Since everything is 0 except for the H6 cell, that's the answer. Google SUMPRODUCT, there are a lot of sites that give more explanation and examples.

As far as the 1/(1/ trick, that's used to show a blank cell instead of a 0. Here's a link to an article I wrote on how that works (option 4), as well as some other ways to do it that might be better for you.

Suppressing zeros

A frequent question on the forums is "how do I not display a zero?" There are several methods to do so, each with their own pluses and minuses. I will explain the various methods here as a guide, and the interested user can choose the one most...

www.mrexcel.com

Good luck!

Thank you very much for your detailed walk-through of the SUMPRODUCT formula, Eric. Thanks a lot for your time and I will spend my time learning it. Much appreciated. All the best,