Value in e3 defines what value goes into q3,r3,s3.

It's not one IF statement, it's three. One each for Q3, R3 and S3. Each will be a nested IF evaluating the value in E3.


Regards, TMS

As Trevor noted, this requires three if statements. So far based on what you've written, put this in Q3 =IF(AND(E3>0,E3<4),1,"") and put this in both R3 and S3 =IF(E3=3,1,""). If you decide to do something else with E3 when it equals 2 for R3 and S3 to equal 1, you could adjust the first formula to be =IF(AND(E3>0,E3<3),1,"") in each R3 and S3. There are other options for if statements too, these are just a couple.

Another option would be to put your variables/data into a small table, then use INDEX/MATCH...

	Q	R	S	T
3	1	1	1
4
5
6		Q	R	S
7	1	1
8	2	1
9	3	1	1	1

Q3=INDEX(R$7:R$9,MATCH($E$3,$Q$7:$Q$9,0))
copied across

My only question is what to do for R and S if E3 = 2, you were not too clear on that

You're welcome. Thanks for the rep.



If you are satisfied with the solution(s) provided, please mark your thread as Solved.


New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save


You may also want to consider thanking those people who helped you by clicking on the little star at the bottom left of their reply to your question.