Search for text in the Cell & return a value

[zaveed]

Hi,

Hoping the experts can help me on this.

I am looking for a formula to find the word/words listed in Column J in the cell A2 and then return the value in the column K. It does not need to be case senstive, Ex: AFI or afi would be the same.

I have data in column A that has the diagnosis . Column J has the word or words that needs to be searched , Column k has the value that it needs to return. Word or words that are being searched can be any where in the column A.

Attached is the excel sheet that has some sample data. i have around 15k rows of diag data, and around 368 values in j & k column.

Thank You.

[AliGW]

Is this for Excel 2016?

In B2 copied down:

=IFERROR(LOOKUP(99999,SEARCH($J$2:$J$4,A2),$K$2:$K$4),"")

[zaveed]

Is this for Excel 2016?

Its for Office 365 and i have excel 2010 also.

[AliGW]

Which does it need to work for? If it's 2010, then that is what needs to be in your profile (and nothing else).

Did you see my solution in post #2?

[zaveed]

Thank you !!, Apologies, i will mention the version that i am working on, have updated the profile.

Have used the formula, verifying some random data. Thank you, will update the thread.

[AliGW]

Your forum profile should show ONLY the OLDEST version you need any solution to work with. Please remove one of the versions listed, as it's confusing as it is. Do this NOW, please.

[AliGW]

Glad to have helped.

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED.

Also, if you have not already done so, you may not be aware that you can thank anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.