create a list of values using index, match

[gtd526]

Hello,
How can I turn the formula below to a list of values beginning at J22?
I want to list all matches that contain "uranium".

INDEX($A$9:$A$15,MATCH("*uranium*",$B$9:$B$15,0))
this is listing the first value that contains 'uranium', but I want ALL values that contains 'uranium'.
looking for any formula that completes the above.

thank you

 

[Scott R]

=FILTER(A9:A15, ISNUMBER( SEARCH( "uranium", B9:B15 )))

 

[Eric W]

Excel 2019 doesn't have FILTER. Try:

Book5
	A	B							I	J
9	1	uranium
10	2	steel
11	3	red uranium
12	4	gold
13	5	uranium mine
14	6	pink
15	7	a uranium factory
16
17
18
19
20
21										Results
22										1
23										3
24										5
25										7
26
Sheet3

Cell Formulas
Range	Formula
J22:J26	J22	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$9:$A$15)/(ISNUMBER(SEARCH("uranium",$B$9:$B$15))),ROWS($J$22:$J22))),"")

 

[gtd526]

Excel 2019 doesn't have FILTER. Try:

Book5
	A	B							I	J
9	1	uranium
10	2	steel
11	3	red uranium
12	4	gold
13	5	uranium mine
14	6	pink
15	7	a uranium factory
16
17
18
19
20
21										Results
22										1
23										3
24										5
25										7
26
Sheet3

Cell Formulas
Range	Formula
J22:J26	J22	=IFERROR(INDEX($A:$A,AGGREGATE(15,6,ROW($A$9:$A$15)/(ISNUMBER(SEARCH("uranium",$B$9:$B$15))),ROWS($J$22:$J22))),"")

Thank you.
I have to remember 'isnumber', doesn't have to be a number.

 

[Eric W]

Glad we could help.

In this case, SEARCH will look for "uranium" in the text, and if it's found, will return the location in the text where it starts (a number). If it's not found, it returns an error. So ISNUMBER checks to see if the result of SEARCH is a number ("uranium" is found somewhere), or an error (not found). It's not checking to see if the text itself is a number. (Although ISNUMBER can do that if applied directly to the text.)