Multiple text cells in formula

[Oberst Hajj]

Hello everyone,

I need to write a formula that will look at the text in cell B14, if it is "Bomber" assign the value of cell C3, if it is "Recon" assign the value of C4, and if it is "Scout" assign the value of C5.

I have to do this for several cells and reference cells, but I think I can figure out how to string them together if I have that first part.

Thanks for any help you can give.

Keith

 

[anandvarma]

try this

Excel Workbook
	B	C
3
4
5		Scout
13
14	Scout
Sheet1

 

[Oberst Hajj]

Thanks anandvarma,

I actually came up with this

=IF(B14="Bomber",$C$3,IF(B14="Recon",$C$4,$C$5))

And that works for me. I now need to do a similar formula, referencing values of other cells based on text, but after all the IF conditions are checked and it assigns the value, I need to multiply the value by .6. Would I simply add

)*.6

At the end of it (with a corresponding opening parentheses just after the IF part of the formula)?

 

[Oberst Hajj]

Nope, looks like I have to add the

*.6

to each of the Value If True parts of the formula.

 

[Michael M]

Hi
a couple of options

=IF(B14="Bomber",$C$3*.6,IF(B14="Recon",$C$4*.6,$C$5*.6))

OR using a cell reference,$D$1, for instance, so that it's dynamic

=IF(B14="Bomber",$C$3*$D$1,IF(B14="Recon",$C$4*$D$1,$C$5*$D$1))

 

[Oberst Hajj]

Your first code is how I did it. And, good point about using the reference cell!

 

[Michael M]

this also works

=(IF(B14="Bomber",$C$3,IF(B14="Recon",$C$4,$C$5))*0.6)

 

[Oberst Hajj]

That's much cleaner and only has one place to edit if you need to change the .6 to something else.

Unrelated, but I'm talking with someone else from NSW on another forum right now. The internet has made the world a small place!

 

[Michael M]

Yep, it has made communications easier.....and sometimes faster !!

 

[Peter_SSs]

Since they all start with a different letter, this may also work for you?

=INDEX($C$3:$C$5,SEARCH(LEFT(B14),"BRS"))*0.6

Not sure if B14 can ever take some other value (or be empty) so just note that as they stand the suggested formulas would return different (and probably unwanted) results in that circumstance. If this could be a posibility, please provide more details.