Return a value based on three fields

[kgagne]

I have a table with three options. Reverse X, Y and Z.

Setting Value	Reverse X	Reverse Y	Reverse Z
0	No	No	No
1	Yes	No	No
2	No	Yes	No
3	Yes	Yes	No
4	No	No	Yes
5	Yes	No	Yes
6	No	Yes	Yes
7	Yes	Yes	Yes

In the spreadsheet I allow the user to select yes or no using a drop-down box.

Reverse X	Reverse Y	Reverse Z
No	Yes	Yes

Based on the values the user selects I want to report that in another cell. In this case the user selected No, Yes and Yes which in the table is 6. I know I can do this with a bunch of If formulas but is there an easier way it can be done with a lookup table? Thanks for any help you can offer. -Kevin

6	Axis Direction

 

[jtakw]

Hi,

Would this work for you:

Book3.xlsx
	A	B	C	D
1	Setting Value	Reverse X	Reverse Y	Reverse Z
2	0	No	No	No
3	1	Yes	No	No
4	2	No	Yes	No
5	3	Yes	Yes	No
6	4	No	No	Yes
7	5	Yes	No	Yes
8	6	No	Yes	Yes
9	7	Yes	Yes	Yes
10
11	Reverse X	Reverse Y	Reverse Z
12	No	Yes	Yes
13
14
15	6	Axis Direction
Sheet1011

Cell Formulas
Range	Formula
A15	A15	=LOOKUP(2,1/FIND(A12&B12&C12,B2:B9&C2:C9&D2:D9),A2:A9)

 

[RasGhul]

Assuming you have Version 365 - Maybe this

Book2
	A	B	C	D
1	Setting Value	Reverse X	Reverse Y	Reverse Z
2	0	No	No	No
3	1	Yes	No	No
4	2	No	Yes	No
5	3	Yes	Yes	No
6	4	No	No	Yes
7	5	Yes	No	Yes
8	6	No	Yes	Yes
9	7	Yes	Yes	Yes
10
11		Reverse X	Reverse Y	Reverse Z
12		No	Yes	Yes
13
14		Setting Value
15		6
Sheet1

Cell Formulas
Range	Formula
B15	B15	=FILTER($A$2:$A$9,($B$2:$B$9=$B$12)*($C$2:$C$9=$C$12)*($D$2:$D$9=$D$12),"")

 

[kgagne]

Hi,

Would this work for you:

Book3.xlsx
	A	B	C	D
1	Setting Value	Reverse X	Reverse Y	Reverse Z
2	0	No	No	No
3	1	Yes	No	No
4	2	No	Yes	No
5	3	Yes	Yes	No
6	4	No	No	Yes
7	5	Yes	No	Yes
8	6	No	Yes	Yes
9	7	Yes	Yes	Yes
10
11	Reverse X	Reverse Y	Reverse Z
12	No	Yes	Yes
13
14
15	6	Axis Direction
Sheet1011

Cell Formulas
Range	Formula
A15	A15	=LOOKUP(2,1/FIND(A12&B12&C12,B2:B9&C2:C9&D2:D9),A2:A9)

That worked great. Thanks so much.

 

[kgagne]

Assuming you have Version 365 - Maybe this

Book2
	A	B	C	D
1	Setting Value	Reverse X	Reverse Y	Reverse Z
2	0	No	No	No
3	1	Yes	No	No
4	2	No	Yes	No
5	3	Yes	Yes	No
6	4	No	No	Yes
7	5	Yes	No	Yes
8	6	No	Yes	Yes
9	7	Yes	Yes	Yes
10
11		Reverse X	Reverse Y	Reverse Z
12		No	Yes	Yes
13
14		Setting Value
15		6
Sheet1

Cell Formulas
Range	Formula
B15	B15	=FILTER($A$2:$A$9,($B$2:$B$9=$B$12)*($C$2:$C$9=$C$12)*($D$2:$D$9=$D$12),"")

That worked great. Thanks so much.

 

[jtakw]

You're welcome, thanks for the feedback.