Apr 20 2022 01:21 AM
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!
Apr 20 2022 01:58 AM
SolutionI'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.
Apr 20 2022 06:54 AM
Apr 20 2022 01:58 AM
SolutionI'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.