Input text in one cell and it populates another cell with different text

[roseyj]

I apologize ahead of time for being confusing. I can clarify if needed.


I pretty much need a contingency spreadsheet to help me make decisions of people.

I want cell C12 to populate with a certain name based on the names being populated in cells C2:C11.

The formula in cell C12 I have tried but only gives me that one answer to one question: =IF(C2="BOB","JOHN","BOB")
So if C2 is populated with "BOB", then C12 will show "JOHN", but if it does not say "BOB then C12 will say "BOB."

I have been unable to get this to work but I would like the IF formula to let me pick the range of cells to do the text search and if the name I am looking for is there it will give me a text output I choose.

This is the formula that will not work:
=IF(C2:C11="BOB","JOHN","BOB")




This is only an example:
Capture.JPG


Thanks so much!

[tamthat]

I don't see data in range A2:A11

[tamthat]

This is the formula that will not work:
=IF(C2:C11="BOB","JOHN","BOB")

C2:C11 in your formulas: C2:C11 = B =>>> Array formulas. You have to use Ctrl+Shift+Enter when finish your formulas!

[roseyj]

Sorry for the typo, it should say C2:C11

[tamthat]

Sorry for the typo, it should say C2:C11

Yes, you can use:

Please Login or Register  to view this content.

[roseyj]

C2:C11 in your formulas: C2:C11 = B =>>> Array formulas. You have to use Ctrl+Shift+Enter when finish your formulas!

The Ctrl+Shift+Enter worked great, thank you!


Is there a way to male this work?

=IF(C2:C4="BOB","JOHN","BOB"), IF(C2:C4="JOHN", "LARRY","LARRY"), IF(C2:C4="LARRY", "JEN","LARRY")



Also, when I put this =IF(COUNTIF($C$2:$C$5,"BOB"),"JOHN","BOB") in to C5, it gives me a 0. Do you know how I could fix this?


Thanks so much

[tamthat]

The Ctrl+Shift+Enter worked great, thank you!


Is there a way to male this work?

=IF(C2:C4="BOB","JOHN","BOB"), IF(C2:C4="JOHN", "LARRY","LARRY"), IF(C2:C4="LARRY", "JEN","LARRY")



Also, when I put this =IF(COUNTIF($C$2:$C$5,"BOB"),"JOHN","BOB") in to C5, it gives me a 0. Do you know how I could fix this?


Thanks so much

Because in your formulas, $C$2:$C$5 include $C$5, you can't use address C5 when you put formulas in C5. You must type: $C$2:$C$4.

[roseyj]

Ahh I see,

Ok last question.. how can I put multiple of this formula in this one cell?

I tried just putting a comma between them like below but this did not work (I had seen that on another Excel help website):

=IF(COUNTIF($C$2:$C$5,"BOB"),"JOHN","BOB"), IF(COUNTIF($C$2:$C$5,"LARRY"),"BOB","LARRY") .... and so on..


Thanks again for your great help

[tamthat]

A problem needs a particular circumstance, I don't have particular problem, I can't help you