CODEWITHSUNDEEP
vbaexcelexcel-formula
steve_o
steve_o

Reputation: 1243

Finding string and returning information from an adjacent cell

Using Excel 2010. I need to see if a string in one set of cells exists in another set of cells, and if so, return information from the adjacent cell that matched that string. I had originally done this with SEARCH, ISNUMBER and nested IF statements, but my source data set has several dozen entries in it, and the strings that must be searched has several hundred entries. The data resembles that in the picture (a simplified example):

enter image description here

For a limited data set, I used nested IF statements, like:

IF(ISNUMBER(SEARCH($D$2,$A2,1)),"Cat Info",IF(ISNUMBER(SEARCH($D$3,$A2,1)),"Dog Info",IF(ISNUMBER(SEARCH($D$4,$A2,1)),"Elephant Info","Not Found")))

But now both sets of data are too large to do that on.

What I need to do is to search the strings in column A for the keyword in column D. If the keyword is found, I need to return the corresponding information from column E.

For example, in column B2, since the word dog is in A2, I would want the contents of E3 (Dog Section) to be displayed in B2.

My list of keywords are unique (Column D, List) and I know that zero or one keyword will appear in the string in Column A (TheString).

I think that INDEX & MATCH functions may be part of my solution, but I am unsure how to find which List keyword is in the string and then return the Information column value.

Upvotes: 0

Views: 1533

Answers (2)

Nathan_Sav
Nathan_Sav

Reputation: 8531

Not done it all, but this formula

=MAX(IF(ISNUMBER(SEARCH(D1,$A$1:$A$4,1)),ROW($A$1:$A$4),0))

Array Formula

Will give you the row of the A1:a4 where D1 etc, is contained. Then you can index on that. However it will only show the max row, so if its in 1 & 2, then it will only show row 2.

Cheers.

Upvotes: -2

Excel Hero
Excel Hero

Reputation: 14764

No need for VBA. This can be done with a simple formula:

Enter this formula in cell B2:

=LOOKUP(2,1/SEARCH(D$2:D$7,A2),E$2:E$7)

Copy downward as far as needed.

Note: adjust the range references to the size of your data.

Upvotes: 5

Related Questions