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
E4	SPC	Grade	Title
E4	SPC
E5	SGT

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet2​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=DAE7F5]#DAE7F5[/URL] "]B1[/TH]
[TD="align: left"]=IFERROR(VLOOKUP(A1,$F$1:$G$10,2,0),"No match")[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]



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!