if cell contains certain text, amongst lots of other text..

[chuf]

morning all,

i have a huge list of data and i want to extract the cost of certain jobs, from column S, if certain text is contained within column F.

ie if it says "kitchen" anywhere within the text in column F then show the costs that relates to that row that is contained in column S.

Are you able to help please. i just can't search the whole text and get it to tell me if that one word is in there.

Many thanks,

 

[Aladin Akyurek]

=INDEX(S:S,MATCH("*"&A2&"*",F:F,0))

where A2 houses a substring of interest.

 

[chuf]

Thanks,

i've just been reading a book on this formula.. and had tried it but it came up with a name error #NAME?

PRIMARY - KITCHEN CANOPY is shown in column G

and because it contains the word kitchen

and i want to show the value in column S in column X

Your formula makes perfect sense and i cannot see why its not working

 

[barry houdini]

Don't see how Aladin's formula would give you #NAME? error unless you have #NAME? errors in your data or if you transcribed the formula incorrectly

Will "kitchen" occur more than once in column G? If so and you want to sum all associated values in column S

=SUMIF(G:G,"*kitchen*",S:S)

or with "kitchen" in A2

=SUMIF(G:G,"*"&A2&"*",S:S)

 

[shippey121]

try this

=IF(ISERROR(FIND(G1,f1,1)),"",IF(FIND(G1,f1,1),s1,""))

where S1 is the cost
F1 is your text
G1 is the word kitchen substitue both G1's for anyother cell

HTH

 

[chuf]

Thanks all,
I'll try when i get home tonight.
Don't know where i'd be without you lot when i'm at my wits end.