• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Retreiving data

N

niting

New Member
Hie again!!


Is it possible to retreive some data based on a word in a long text???


I have a column with some text and value corrosponding to it in another column. Now i want to retreive value if a particular word is anywhere in the long text. eg i want to pick value against the word "chandoo" if the text contains " Chandoo blog is amazing"


I tried Vlookup, but dint work. Any suggestions???


Thanks

Nitin
 
Hie Hui,


I believe i wasnt able toexplain the problem. i go again-


Col A Col B

Chandoo is amazing 1000


Now what i want is, that if my criteria is "chandoo" then excel returns the value in Col B against the text "chandoo is amazing" or sum the values for the rows where "chandoo" appears.


Thanks

Nitin
 
assuming your using row 2

Excel 2007/10 =IFERROR((SEARCH("Chandoo",A2)>0)*B2,"")

Excel up to 2003 =IF(ISERROR(SEARCH("Chandoo",A2)>0),"",B2)
 
Hui,


Thanks a lot. I just wonder how you people combine so many functions to achieve desired results!!! Gr8 work.


About the solution, it worked, but does it work on a range , say A1: A200 or we have to copy paste the formula for each row??


Secondly, i tried to make sense of the formula, search function returns the position of the text within the text and we multiply the value so obtained in the solution by the value, so how come we only get the value and not the result after multipicaion.??


Thanks a lot for the help.
 
Assuming you have data in Row 2

Copy the formula to C2

Then copy C2 and paste it in C3:C100

Excel will sort out the references automatically

If your using a later version of Excel Select C2 and Double Click the small black box at the bottom right corner of C2 and it will copy down automatically


The part Search("Chandoo",A2)>0 will return True if it finds a result and an error if it doesn't.

Multiplying True x B2 will convert True to a 1 and hence you get the value of B2.

The Iferror is there to look after when search doesn't find a value.
 
I may be showing my ignorance, but what function does the >0 part serve? I tried the formula without it and it still work. What scenario throws off the formula, so that you need the >0?


Thanks,

Alan
 
You must log in or register to reply here.
Back
Top