if Statement with Multiple Conditions Help Please

Select Case or a multi-dimensional array may be appropriate, but without understanding exactly what you're trying to do with each variable combination, it's hard to advise specifically...

You could set up a table of the combinations and possible outcomes and then use VLOOKUP to match the concatenated values to the Table

	A	B	C	D	E	F	G	H
1	X	Y	Z	XYZ	Outcome
2	X1	Y1	Z1	X1Y1Z1	Outcome 1		x1
3	X1	Y1	Z2	X1Y1Z2	Outcome 2		y2
4	X1	Y1	Z3	X1Y1Z3	Outcome 3		z3
5	X1	Y2	Z1	X1Y2Z1	Outcome 4
6	X1	Y2	Z2	X1Y2Z2	Outcome 5		x1y2z3	=G2&G3&G4
7	X1	Y2	Z3	X1Y2Z3	Outcome 6		Outcome 6	=VLOOKUP(G2&G3&G4,D:E,2,FALSE)
8	X1	Y3	Z1	X1Y3Z1	Outcome 7		Outcome 6	=VLOOKUP(G6,D:E,2,FALSE)
9	X1	Y3	Z2	X1Y3Z2	Outcome 8
10	X1	Y3	Z3	X1Y3Z3	Outcome 9
11	X1	Y4	Z1	X1Y4Z1	Outcome 10
12	X1	Y4	Z2	X1Y4Z2	Outcome 11
13	X1	Y4	Z3	X1Y4Z3	Outcome 12
14	X1	Y5	Z1	X1Y5Z1	Outcome 13
15	X1	Y5	Z2	X1Y5Z2	Outcome 14
16	X1	Y5	Z3	X1Y5Z3	Outcome 15

etc ...


Regards, TMS

Originally Posted by Olly

Select Case or a multi-dimensional array may be appropriate, but without understanding exactly what you're trying to do with each variable combination, it's hard to advise specifically...

Hope this diagramm of the logic help explain what I am trying to achieve


Thank you

Please Login or Register  to view this content.

Here's another approach:

Please Login or Register  to view this content.

No, you need a table with all 135 options. Some of the outcomes will be duplicated/replicated/whatever. You would build a key from the X, Y and Z values and then use that key to look in column D and return the outcome from column E.

Regards, TMS

Okay, your criteria / output mapping is different now to your initial example, so we can take the loops out. Function now looks like:

Please Login or Register  to view this content.

Now, if your MyX, MyY and MyZ variables are in columns E, F and G respectively, then you could write the formula using:

Please Login or Register  to view this content.

Now, looking at this comment:

'that is the result I get =VLOOKUP(XFC2, Sheet2!P:XFC, lookatcol(MyX,MyY,MyZ), FALSE), guess because I have not set my criteria

I'm guessing you don't really want to be referencing column XFC, so it looks like your relative column references aren't quite what you wanted....

It's quite hard to provide a specific solution without a specific example outcome to work towards!

It's quite hard to provide a specific solution without a specific example outcome to work towards!

here is the example with input and out put, makes it easier to nail this,

Thank you!

PS : on sheet Outcome, there will be many rows, so the formula need to loop, as far Column A last`s row.

So why not just use:

Formula:

Please Login or Register  to view this content.

Originally Posted by bimo

WOW one powerfull formulae !! ,,will I be able to add more variables to it ?

Yes, you can add further criteria to the SUMIFS formula.