IF, AND, & OR Functions

[srpullar91]

I am looking to create (if possible) a rather lengthy formula which will give me the following result.

If the word in column B = "x" and the number in column E = "1" then result in column G = "z", OR if Column E = "2" then result in column G = "zz", OR if Column E = "3" then result in column G = "zzz" 

And so on up to a maximum number of 7 in column E.

However Column B also has 4 variables "x", "x1", "x2" and "x3", 

So a very lengthy formula - is this even possible and if so please help me in constructing it?

Thanks in advance for any help!

[#2 · Accepted solution]

@srpullar91 

I'd use a two-way lookup table, as shown in the screenshot in J2:N9.

The formula in G2 is

=INDEX($K$3:$N$9,MATCH(E2,$J$3:$J$9,0),MATCH(B2,$K$2:$N$2,0))

It looks for the value of E2 in J3:J9 and for the value of B2 in K2:N2 and uses those to return the corresponding value in K3:N9.

The formula can be filled down.

[#3]

@Hans Vogelaar 

This is fantastic. Thank you so much for your assistance! :)