CODEWITHSUNDEEP
excel
HoekPeter
HoekPeter

Reputation: 11

Excel check if cell contains text from list and return value from list

I've seen many posts about the subject, but in general the question has been to return the value of a cell if the cell contains text from a list. I have used the following formula for that: 

=IF(OR(INDEX(COUNTIF(Cell;"*"&Array&"*");));1;0)

However, what I want is to check if a cell contains text from a list, and if this is TRUE then show the text from the list, not the cell. A small example below.

In column A I have the following values

abc123
def123
ghi123
jkl123

In column B (the list)

abc
def
jkl

Now the first cell (abc123) is in the list, i want to show the text from the list (abc) in the formula cell.

I hope this is clear.

Upvotes: 0

Views: 22003

Answers (1)

basic
basic

Reputation: 11968

You can use following array formulas:

  • If the text you are searching is always placed at the beginning of the string

    {=INDEX($B$1:$B$3;MATCH(1;SEARCH(TRANSPOSE($B$1:$B$3);A1);0);0)}

  • or if the text you are searching is placed anywhere

    {=INDEX($B$1:$B$3;MATCH(1;--(SEARCH(TRANSPOSE($B$1:$B$3);A1)>0);0);0)}

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

enter image description here

Upvotes: 2

Related Questions