Could I have such IF statement?

[volcano]

Hello, I have an Excel question that I would like to ask for your solution. Thanks!

I have a sample Excel file like the below:
******************************************
A B C D E F
1 Name Works for Dept Name2 Dept2
2 Mary Tom IT
3 Tom Tom IT
4 Ada Tom IT
5 John Tom IT
6 Tom Tom IT
******************************************

Now I write a formula in cell F1. Is it possible that:
1) if A1 = D1, then B1 = "Works for" AND C1 = E1;

2) D1 and E1's values can change to, say, Ada and HR. Could I do the same logic as question 1 and RETAIN my previous results like "Works for" and "IT" in B1 and C1?

Suppose I have total three entries: "Tom, IT", "Ada, HR" and "John, Sales". My final result in the excel should be:
******************************************
A B C
1 Name Works for Dept
2 Mary
3 Tom Works for IT
4 Ada Works for HR
5 John Works for Sales
6 Tom Works for IT
******************************************

Thanks for reading my long question and hope to see your feedback. Thanks!

 

[dawnd3]

Hi there,

For question 1, you would have to put the formula in the cell that you want the results. So, in cell B1 you would put =IF(A1=D1,"works for","&quot, and in C1 you would put =If(A1=D1,C1,"&quot (in both cases the empty quotes "" mean no text, you can exchange that for something else.)

For question 2. If your value in D1 changes then that changes the result of the If Statement. You would then see whatever value you entered into the 3rd arguement where the "" currently is.)

Hope this helps.I am not quite sure what you are trying to accomplish with this task, if you explain a bit more maybe there is a better solution.

Dawn

http://www.calliopeconsulting.net