combining formulas to make two possible answers

[Hollywood9819]

I am trying to combine two formulas in a single cell to give me one of two possible answers in an adjacent cell. For example: I have a list of soldiers ranking E4 and E5 in column A. In column B I want to provide a formula that will decipher E4 to SPC and E5 to SGT. Can anybody assist in this?

 

[steve the fish]

Hi. You will have to give us the formulas you are using.

 

[Hollywood9819]

Ok, sorry about that. The two I am using to get either/or but not the combination of is this: for SPC = =IF(A1=”E4”,”SPC”) for SGT = =IF(A1=”E5”,”SGT”)
Those seem to work on an individual basis, but when trying to combine them I'm having no luck.
Thank you!

 

[steve the fish]

When you say "E4" do you literally mean E4 or do you mean the cell E4?

 

[Hollywood9819]

I mean literally E4 or E5 (as the text within the cells in the "A" column.

 

[steve the fish]

This should do that:

=IF(A1="E4","SPC",IF(A1="E5","SGT","Neither"))

 

[Eric W]

You might also want to consider one of the lookup functions:

	A	B	C	D	E	F	G
1	E4	SPC				Grade	Title
2						E4	SPC
3						E5	SGT
4
5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet2​

Worksheet Formulas Cell Formula B1 =IFERROR(VLOOKUP(A1,$F$1:$G$10,2,0),"No match") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

In a setup like this, if you ever want to add E6, E7, etc., all you'd need to do is add them to the table - you wouldn't need to change the formula.

 

[Hollywood9819]

Wow, it worked! Thank you! I have been trying to figure this out for a few days, using the combination of IF and OR, or the & symbol. Thank you very much!