Returning a value from an expansive list of value

[gcandido]

Hi there,

I am trying to return one value from a list of values where this list will be growing in the future. So the spreadsheet looks like something as below

Where column D will grow in the future. The issue I found so far is that the SEARCH formula only looks for one row at a time. Using =IF(OR(IFERROR(SEARCH($D$2,A2,1),0),IFERROR(SEARCH($D$3,A2,1),0),"Others")) is not an option as the Category list 1 grows, the OR starts to become extremely large. Is there another way to do that? any ideas? thanks a lot in advance

Gabriel

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff v2.xlsm
	A	B	C	D
1	Text	Cats		Cat 1
2	the blue sky	Cat 1		sky
3	The red fox	Cat 1		Red
4	the blue lake	Others
5
Main

Cell Formulas
Range	Formula
B2:B4	B2	=IF(MAX(IFERROR(SEARCH($D$2:$D$3,A2),0)),"Cat 1","Others")

 

[Peter_SSs]

Another option if just looking at a single category.

But note row 5. That sort of thing could happen with your original formula too, or Fluff's.
From your sample data I suspect that you do not want that result since you appear to be searching for words rather than text strings. If so see the second sheet below.

20 11 08.xlsm
	A	B	C	D
1	Text	Cats		Cat 1
2	the blue sky	Cat 1		sky
3	The red fox	Cat 1		Red
4	the blue lake	Others
5	credit rating	Cat 1
Category 1

Cell Formulas
Range	Formula
B2:B5	B2	=IF(COUNT(SEARCH(D$2:D$3,A2)),D$1,"Others")

20 11 08.xlsm
	A	B	C	D
1	Text	Cats		Cat 1
2	the blue sky	Cat 1		sky
3	The red fox	Cat 1		Red
4	the blue lake	Others
5	credit rating	Others
Category 2

Cell Formulas
Range	Formula
B2:B5	B2	=IF(COUNT(SEARCH(" "&D$2:D$3&" "," "&A2&" ")),D$1,"Others")

 

[Peter_SSs]

.. or are you looking to select from multiple categories at once more like your original sample?
Note (rows 9 & 10) that if matches are made from multiple categories, the left-most category is returned.

20 11 08.xlsm
	A	B	C	D	E	F
1	Text	Category		Category 1	Category 2	Category 3
2	car is blue	Category 1		Blue	Red	Shy
3	the girl is shy	Category 3		Book		Houses
4	the book is on the table	Category 1		Banana
5
6	all houses are yellow	Category 3
7	credit rating
8	I ate a banana	Category 1
9	I have a red book	Category 1
10	I have a blue book	Category 1
11
Category 3

Cell Formulas
Range	Formula
B2:B11	B2	=IF(A2="","",IFERROR(INDEX(D$1:F$1,AGGREGATE(15,6,(COLUMN(D$2:F$20)-COLUMN(D$2)+1)/ISNUMBER(SEARCH(" "&D$2:F$20&" "," "&A2&" ")),1)),""))

 

[gcandido]

Thanks a lot, @Peter_SSs The last post is the closest to what I want to achieve. But when I am formatting the formula in my spreadsheet it is not capturing other categories. Could you explain how to expand also to more categories? I am having so far around 15 categories.

best regards

 

[Peter_SSs]

Could you explain how to expand also to more categories?

=IF(A2="","",IFERROR(INDEX(D$1:F$1,AGGREGATE(15,6,(COLUMN(D$2:F$20)-COLUMN(D$2)+1)/ISNUMBER(SEARCH(" "&D$2:F$20&" "," "&A2&" ")),1)),""))

Assuming your layout is similar to what I have but the extra categories are in columns G, H, I, J etc then change the red 'F' values above to the last category column, or even a column further to the right than that if you might add extra categories later.

You can also adjust the blue 20 values above if required. That simply needs to be a row at least as great as the greatest row number used in the category columns.