Return Array Value If That Value Exists Within A Text String

[a_faulding]

Hello,

I am building a spreadsheet and want to check whether a value in an array exists within a text string. If the value within the array does exist within the text string, I would like to return the array value.

For example:

String	Desired Result		Array Column 1
Tasty Apples	Apple		Apple
Pomegranate	-		Banana
Orange Peel	Orange		Pear
123Pear	Pear		Orange
Pineapple	-		Grape

If possible, I would also like to be able to do this with a multi-column array, e.g:

String	Desired Result		Array Column 1	Array Column 2	Array Column 3
Tasty Apples	Apple		Banana	Orange	Grape
Pomegranate	-		Apple	Mango	Strawberry
Orange	Orange		Mango	Grape	Mango
123Pear	Pear		Grape	Strawberry	Apple
Pineapple	-		Strawberry	Mango	Pear

And finally...

Could I do this by referencing a named table instead of a selection, so if new values get added to the bottom of the table, the formula automatically references the full table?

Thanks!

Andrew

 

[ER_Neha]

Hello I tried solving the first part of the question. I hope this helps.

Book1
	A	B	C	D
1	String	Desired		Array Column 1
2	Tasty Apples	Apple		Apple
3	Pomegranate	-		Banana
4	Orange Peel	Orange		Pear
5	123Pear	Pear		Orange
6	Pineapple	-		Grape
Sheet1

Cell Formulas
Range	Formula
B2:B6	B2	=IFERROR(INDEX($D$2:$D$6,IF(SUMPRODUCT(--ISNUMBER(FIND($D$2:$D$6,A2)),MATCH(ROW(D2:D6),ROW(D2:D6)))>0,SUMPRODUCT(--ISNUMBER(FIND($D$2:$D$6,A2)),MATCH(ROW(D2:D6),ROW(D2:D6)))," ")),"-")

 

[Peter_SSs]

Will it ever be possible that more than one array value might be found in the text? eg "I like Apple and Pear"

Assuming that the answer is "No" or "Yes but I just want any one of them returned", then see below.

I am also assuming that this is a case-sensitive search since you want to find "Apple" in "Apples" but you do not want to find "apple" in "Pineapple".

So for the first question

20 08 20.xlsm
	A	B	C	D
1	String	Desired		Array Column 1
2	Tasty Apples	Apple		Apple
3	Pomegranate			Banana
4	Orange Peel	Orange		Pear
5	123Pear	Pear		Orange
6	Pineapple			Grape
7	I like Apple and Pear	Pear
Array Value

Cell Formulas
Range	Formula
B2:B7	B2	=IFERROR(LOOKUP(9.99E+307,FIND($D$2:$D$6,A2),$D$2:$D$6),"")

Could I do this by referencing a named table instead of a selection,

See if this would work for you.

Turn the columns of values into a formal Excel table (mine is called 'Table1') then try this

20 08 20.xlsm
	A	B	C	D	E	F
1	String	Desired		Fruit 1	Fruit 2	Fruit 3
2	Tasty Apples	Apple		Banana	Orange	Grape
3	Pomegranate			Apple	Mango	Strawberry
4	Orange Peel	Orange		Mango	Grape	Mango
5	123Pear	Pear		Grape	Strawberry	Apple
6	Pineapple			Strawberry	Mango	Pear
7	I like Apple nad Pear	Apple
Array Value (3)

Cell Formulas
Range	Formula
B2:B7	B2	=TRIM(LEFT(SUBSTITUTE(TEXTJOIN(",",1,IF(ISNUMBER(FIND(Table1,A2)),Table1,"")),",",REPT(" ",50)),50))

 

[a_faulding]

Thank you both for your responses.

Good spot regarding pineapple! I was too busy thinking about different fruits to realise it also contained "apple"!..

Peter - your last suggestion seems perfect. I actually did not want it to be case-sensitive, so I just swapped out FIND for SEARCH.

Cheers!

Andrew

 

[Peter_SSs]

I actually did not want it to be case-sensitive, so I just swapped out FIND for SEARCH.

So you are happy to have Apple returned for Pineapple? (or Pear returned for Appearance?)

 

[a_faulding]

Hi Peter, in the context of the actual sheet, that will not be an issue but thanks for making me aware.

One thing I did just notice, is that I cannot use the returned values from your suggestion for a secondary lookup as there are loads of apparent spaces after the value - despite using the TRIM function.

Is there a solution for getting rid of these surplus spaces to return a clean string?

 

[Peter_SSs]

One thing I did just notice, is that I cannot use the returned values from your suggestion for a secondary lookup as there are loads of apparent spaces after the value - despite using the TRIM function.

I don't have that - see below in column H that the right hand 3 letters of the result are exactly as they appear to be in column B. Must be something different about your data.

Can you post a small sample with XL2BB so that we can copy exactly what you have?

20 08 20.xlsm
	A	B	C	D	E	F	G	H
1	String	Desired		Fruit 1	Fruit 2	Fruit 3
2	Tasty Apples	Apple		Banana	Orange	Grape		ple
3	Pomegranate			Apple	Mango	Strawberry
4	Orange Peel	Orange		Mango	Grape	Mango		nge
5	123Pear	Pear		Grape	Strawberry	Apple		ear
6	Pineapple	Apple		Strawberry	Mango	Pear		ple
7	I like Apple nad Pear	Apple						ple
8	Appearance	Pear						ear
Array Value (3)

Cell Formulas
Range	Formula
B2:B8	B2	=TRIM(LEFT(SUBSTITUTE(TEXTJOIN(",",1,IF(ISNUMBER(SEARCH(Table1,A2)),Table1,"")),",",REPT(" ",50)),50))
H2:H8	H2	=RIGHT(B2,3)

 

[a_faulding]

Hmm strange. Afraid I can't install XL2BB (work computer) but I did manage to create a workaround after cleaning my data and creating a table called Fruit, with just one column:

{=IFERROR(INDEX(Fruit,MATCH(TRUE,ISNUMBER(SEARCH(Fruit,A2)),0)),"")}

Could this formula be modified to work with a table with multiple columns?

Thanks,

Andrew

 

[Peter_SSs]

Afraid I can't install XL2BB

Could you upload a small sample file with dummy data to DropBox, Google Drive etc and provide a shared link here?
If so, can you also include whatever is required to demonstrate the "loads of apparent spaces" issue described in post #6?

Also, what about the answer to this question that I asked earlier?

Will it ever be possible that more than one array value might be found in the text? eg "I like Apple and Pear"