Return a value based on multiple variables

[LodgeMD]

I'm working on a spreadsheet and I want to return a specific value based on different variables.

In column D the user can select from two different values (Simp or Audit), then in column H they can select one of 3 values (Val1, Val2 or Val3). Based on what is selected in these 2 columns I want a specific response in column I.

So if:
Simp + Val1 = Answer 1
Simp + Val2 = Answer 2
Simp + Val3 = Answer 3
Audit + Val1 = Answer 4
Audit + Val2 = Answer 5
Audit + Val3 = Answer 6

How can I code this in VBA?

Thanks for your help

 

[Comfy]

Allocate a Value to Each variable:

Simp = 1
Audit = 2
Val1 = 1
Val2 = 2
Val3 = 3

Put your answers into an Array.

AnswerArr(1 To 2, 1 To 3)

AnswerArr(1,1) = "Answer 1"
.....
AnswerArr(2,3) = "Answer 6"

 

[pgc01]

Hi

Just out of curiosity, why vba? This is such a simple problem, why not just a formula?

 

[LodgeMD]

Thanks for your comment.

I'm quite inexperiances in using complex formulea in Excel so I thought VBA was the best route. Would you be able to share the formula with me?

Regards

 

[LodgeMD]

Hi Comfy

Thanks for your advice.

 

[Comfy]

Hi Comfy

Thanks for your advice.

You're Welcome.

Thanks for your comment.

I'm quite inexperiances in using complex formulea in Excel so I thought VBA was the best route. Would you be able to share the formula with me?

Regards

There a many ways to skin a cat!!


Excel 2010

	B	C	D
Simp	Val1	Answer 1
Simp	Val2	Answer 2
Simp	Val3	Answer 3
Audit	Val1	Answer 4
Audit	Val2	Answer 5
Audit	Val3	Answer 6

<colgroup><col style="width: 25pxpx"><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]3[/TD]

[TD="align: center"]4[/TD]

[TD="align: center"]5[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D3[/TH]
[TD="align: left"]=INDEX({"Answer 1","Answer 2","Answer 3";"Answer 4","Answer 5","Answer 6"},MATCH(B3,{"Simp","Audit"},0),MATCH(C3,{"Val1","Val2","Val3"},0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Excel 2010
	B	C	D	E	F	G	H	I	J
2							Val1	Val2	Val3
3	Simp	Val1	Answer 1			Simp	Answer 1	Answer 2	Answer 3
4	Simp	Val2	Answer 2			Audit	Answer 4	Answer 5	Answer 6
5	Simp	Val3	Answer 3
6	Audit	Val1	Answer 4
7	Audit	Val2	Answer 5
8	Audit	Val3	Answer 6
9
Sheet1

Cell Formulas
Range	Formula
D3	=INDEX($H$3:$J$4,MATCH(B3,$G$3:$G$4,0),MATCH(C3,$H$2:$J$2,0))