combining formulas to make two possible answers

Hollywood9819

New Member
Joined
Dec 11, 2017
Messages
10
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?
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,944
Office Version
365
Platform
Windows
Hi. You will have to give us the formulas you are using.
 

Hollywood9819

New Member
Joined
Dec 11, 2017
Messages
10
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

Well-known Member
Joined
Oct 20, 2009
Messages
7,944
Office Version
365
Platform
Windows
When you say "E4" do you literally mean E4 or do you mean the cell E4?
 

Hollywood9819

New Member
Joined
Dec 11, 2017
Messages
10
I mean literally E4 or E5 (as the text within the cells in the "A" column.
 

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
7,944
Office Version
365
Platform
Windows
This should do that:

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
9,335
You might also want to consider one of the lookup functions:

ABCDEFG
1E4SPCGradeTitle
2E4SPC
3E5SGT
4
5

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

Worksheet Formulas
CellFormula
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

New Member
Joined
Dec 11, 2017
Messages
10
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,090,457
Messages
5,414,637
Members
403,539
Latest member
rthompsona

This Week's Hot Topics

Top