SOLVED

New Contributor

# IF, AND, & OR Functions

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 Replies
best response confirmed by Sergei Baklan (MVP)
Solution

# Re: IF, AND, & OR Functions

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.

# Re: IF, AND, & OR Functions

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