CODEWITHSUNDEEP
excelexcel-formula
Dan C
Dan C

Reputation: 15

Search multiple columns for text contained within another column, return a value

I apologize if this is a silly question, or if it's been asked before, but I have searched all over and haven't found a similar question/solution. I need to look within two possible columns (A and B) for text contained in another column (E). If text contained in column A or B is found within column E, I need the value in column C to appear in column F.

The text in column A and B will not be identical to that in column E (if it were I wouldn't be asking this question). And there may or may not be data in column B (not sure if that adds any complexity, but just for clarity).

I've tried all sorts of combinations of INDEX(MATCH and ISNUMBER(SEARCH, etc. but to no avail. I'm pretty sure there is a simple solution for this. Any help would be greatly appreciated!

![enter image description here

Upvotes: 0

Views: 1404

Answers (2)

basic
basic

Reputation: 11968

You can use following array formula:

{=INDEX(F$2:$F$6,MATCH(1,--(MMULT(--ISNUMBER(SEARCH(IF(ISBLANK(B2),A2,A2:B2),$E$2:$E$6)),TRANSPOSE(COLUMN(IF(ISBLANK(B2),A2,A2:B2))^0))>0),0))}

Array formula after editing is confirmed by pressing ctrl + shift + enter

enter image description here

EDIT:

Reverse search - from column C to column F. Also an array formula.

{=INDEX($C$2:$C$9,MATCH(1,--(MMULT(--ISNUMBER(SEARCH(IF(ISBLANK($A$2:$B$9), $A$2:$B$9 & "#",$A$2:$B$9),E2)),TRANSPOSE(COLUMN($A$1:$B$1)^0))>0),0))}

In the expression $A$2:$B$9 & "#" instead of # you can specify any character that is not used in the search text

enter image description here

Upvotes: 1

Dadapawa
Dadapawa

Reputation: 16

Not sure if there is an easier way, but with some validation you can easily come up with something like

=IF(OR(IF(A3<>"",ISNUMBER(SEARCH(A3,E3)),FALSE),IF(B3<>"",ISNUMBER(SEARCH(B3,E3)),FALSE)),C3,F3)

It verify that both A3 and B3 are not empty (<>"",...,FALSE) and return false if so. It than make twice the same validation for A3 and B3; Search if the cell is contained within the cell E3. If any of the two above is TRUE, than the whole IF is true, and it will return C3 as you need. You didn't mention what you required in the case that the string was not found, so in my exemple, it return the value in F3 (but you could replace it with whatever).

Hope this help!

Upvotes: 0

Related Questions