Need a little help with formulas and specific text

[kittyf]

I am using Excel 2007 and i am trying to see if there is a way to do a formula for the below problem.

I want to put a formula in cell C1 that looks for specific text in A1 (the text would be a couple of different abbreviations CT, CE, CC, CD) and if one of these abbreviations are in A1 then put the number value that is in B1 within C1.

Second issue is if one of these abbreviations are not in A1 then i want no return in C1.

I hope this makes sense to everyone.

Any an all assistance would be greatly appreciated.

 

[Jonmo1]

Welcome to the board...

Try this formula in C1

=IF(ISNUMBER(LOOKUP(2^15,SEARCH({"CT","CE","CC","CD"},A1))),B1,"")


Hope that helps.

 

[Andrew Poulsom]

Welcome to MrExcel.

Try:

=IF(SUM(COUNTIF(A1,{"CT","CE","CC","CD"})),B1,"")

 

[kittyf]

Welcome to the board...

Try this formula in C1

=IF(ISNUMBER(LOOKUP(2^15,SEARCH({"CT","CE","CC","CD"},A1))),B1,"")


Hope that helps.

jonmo1,

Since i am just about finished with my form i realized i need to have C1 look at multiple cells A1, A2, A3, etc for the abbreviations CT,CE,CC,CD, and then input the numeric values from B1, B2, B3, etc into C1. I did try putting the formula into c1 and changing the range, but the cell came up empty.

How do i change up the formula you had sent me to accomodate this.

 

[Jonmo1]

I think something needs to be clarified..
Since Andrew posted a different formula, that will work as well depending on your data.

Will A1 contain other text as well as the abbreviations?
Like this for example
"The code is CC"

Or will the abbreviations appear by themselves in A1?


Also, can you post some sample data and expected results?
Use Excel Jeanie for that, see my signature for a link to it.

 

[kittyf]

Jonmo1,

I had started using your formula first and then i had seen Andrews respose. Andrews formula also worked perfectly, but the same issue came up that the cell would go blank if i expanded the range of cells i wanted to ge tthe infomration from.

To answer your question; A1 would only show the abbreviation and also only one abbreviation at a time. but I need C1 to look at A1, A2 and and then take the numeric value from B1, B2 and add them in C1.

I really appreciate the assistance you are giving me.

 

[Jonmo1]

Ahh, the key word is ADD..

Try this similar to Andrew's but using SUMIF instead of COUNTIF

=SUM(SUMIF(A1:A10,{"CT","CE","CC","CD"},B1:B10))

 

[kittyf]

Andrew,

I have been conversing with Jonmo1 and both of you gave me a formula that worked with my problem but i have since completed my form but i have another problem that i was unaware of. I need to have C1 look at multiple cells A1, A2, A3, etc for the abbreviations CT,CE,CC,CD, and then input the numeric values from B1, B2, B3, etc into C1. I did try putting the formula into c1 and changing the range, but the cell came up empty.

How do i have C1 look at a range of cells instead of one? <!-- / message -->

 

[kittyf]

Jonmo1,

PERFECT!! Thank you very much.