CODEWITHSUNDEEP
excel
Kalenji
Kalenji

Reputation: 407

search and assign value based on text

I tried using formulas (search, sumproduct, if) to search for a string and return a specific value. So in my example, if A1 contains string with "AAA", return 1, if A1 text has "BBB", return 2 etc...Any ideas

text              value
MAINTENANCE AAA     1
MAINTENANCE BBB     2
CCC MAINTENANCE     3

Thanks in advance.

Upvotes: 0

Views: 514

Answers (2)

barry houdini
barry houdini

Reputation: 46401

Make a table of texts and associated values, e.g. in Y2 you have AAA and in Z2 1, In Y3 BBB, in Z3 2 etc. then you can use this formula

=INDEX(Z$2:Z$10,MATCH(1,INDEX(ISNUMBER(SEARCH(Y$2:Y$10,A2))*(Y$2:Y$10<>""),0),0))

enter image description here

Note: SEARCH is not case-sensitive - if you want the search to be case-sensitive then use FIND function instead of SEARCH

Upvotes: 1

Edvins
Edvins

Reputation: 426

One way to do this without having some kind of "mapping table" would be to use two arrays - one for text parts to look for and the other for values:

=IFERROR(INDEX({1,2},MATCH(TRUE,ISNUMBER(SEARCH({"aaa","bbb"},A2)),0)),"Not found")

Where {1,2} is the array of values and {"aaa","bbb"} is the array of texts to look for and A2 is the cell containing text to be searched.

Upvotes: 1

Related Questions