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

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

steve the fish

Well-known Member
Joined
Oct 20, 2009
Messages
8,387
Office Version
  1. 365
Platform
  1. 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
8,387
Office Version
  1. 365
Platform
  1. 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

ADVERTISEMENT

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
8,387
Office Version
  1. 365
Platform
  1. Windows
This should do that:

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

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,741
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,127,062
Messages
5,622,466
Members
415,896
Latest member
Blooze

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top