If a cell contains a string, return a value from an array

[topi1]

In the following great formula/example from @Eric W, is it possible to change the formula in B, where it will give results even if not separated by whitespace and delimiters. It will help if it at least ignores delimiters like ".", "?" and "!". Thank you.

Book1
	A	B	C
27	Sample	Output	Key
28	Red wine on the carpet	Red	Red
29	Blue -like the color or the mood	Blue	Blue
30	yellow -testing if capitalization matters	Yellow	Yellow
31	xxgreenxx if the string needs to be isolated by whitespace or delimiters	no match	Green
32	Red	Red
33	Blue	Blue
34	Green	Green
35	Yellow	Yellow
Sheet1

Cell Formulas
Range	Formula
B28:B35	B28	=IFERROR(LOOKUP(2,1/SEARCH(" "&$C$28:$C$35&" "," "&A2&" "),$C$28:$C$35),"no match")

 

[DanteAmor]

Check if any of the 2 options are what you need:

Option 1:

varios 29ene2024.xlsm
	A	B	C
27	Sample	Output	Key
28	Red wine on the carpet	Red	Red
29	Blue -like the color or the mood	Blue	Blue
30	yellow -testing if capitalization matters	Yellow	Yellow
31	xxgreenxx if the string needs to be isolated by whitespace or delimiters	Green	Green
32	Red	Red
33	Blue	Blue
34	Green	Green
35	Yellow	Yellow
sh3

Cell Formulas
Range	Formula
B28:B35	B28	=IFERROR(LOOKUP(2,1/SEARCH(IF($C$28:$C$35<>"",$C$28:$C$35),A28),$C$28:$C$35),"no match")
Press CTRL+SHIFT+ENTER to enter array formulas.

Option 2:

varios 29ene2024.xlsm
	A	B	C
27	Sample	Output	Key
28	Red wine on the carpet	Red	Red
29	Blue -like the color or the mood	Blue	Blue
30	yellow -testing if capitalization matters	Yellow	Yellow
31	xx green! if the string needs to be isolated by whitespace or delimiters	Green	Green
32	Red	Red
33	Blue	Blue
34	Green	Green
35	Yellow	Yellow
sh4

Cell Formulas
Range	Formula
B28:B35	B28	=IFERROR(LOOKUP(2,1/SEARCH(" "&$C$28:$C$35&" "," "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A28,"!",""),".",""),"?","")&" "),$C$28:$C$35),"no match")

 

[Peter_SSs]

Using Excel for text tasks quite often gives results that are less than perfect and you have to settle for the one that best suits your purpose at the time. So here is yet another option using vba to provide a user-defined function. Post back if you need implementation instructions.
Differences (could be good or bad?) with this one include.

1. Multiple Keys can be returned as in row 38 below. (Could be restricted to one if you wanted)
2. The upper/lower case of the result comes from the string being tested, not the Key list like the above formula options. (This could be altered with a bit more work.)
3. There must be no blank cells in the 'Key' range. (This could be allowed for with some more work)
4. This function readily handles more (but possibly not all) punctuation-like delimiters. One extra example below is the 'no-space hyphen' in row 30
5. The function can readily be made case-sensitive by including FALSE as the optional 3rd argument in the function as shown in column D
6. I was not sure about your intention/desire with the xxgreenxx example you gave but assume that you probably would not want that matched otherwise you could get a lot of matches with short words like "red" since it can be part of many longer words. I gave one extra example in row 37.

I have included @DanteAmor's suggestions in columns E & F so that you can easily see the various differences between all our suggestions & perhaps help choose which (if any) are best for your circumstances.

Function CheckKeys(s As String, rKeys As Range, Optional bIgnoreCase As Boolean = True) As String
  Dim RX As Object, m As Object
  
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = bIgnoreCase
  RX.Pattern = "\b(" & Join(Application.Transpose(rKeys), "|") & ")\b"
  For Each m In RX.Execute(s)
    CheckKeys = CheckKeys & ", " & m
  Next m
  If Len(CheckKeys) = 0 Then
    CheckKeys = "no match"
  Else
    CheckKeys = Mid(CheckKeys, 3)
  End If
End Function

topi1_2.xlsm
	A	B	C	D	E	F
27	Sample	Output	Key
28	Red wine on the carpet	Red	Red	Red	Red	Red
29	Blue -like the color or the mood	Blue	Blue	Blue	Blue	Blue
30	Blue-like the color or the mood	Blue	Yellow	Blue	Blue	no match
31	yellow -testing if capitalization matters	yellow	Green	no match	Yellow	Yellow
32	xxgreenxx if the string needs to be isolated by whitespace or delimiters	no match		no match	Green	no match
33	Red	Red		Red	Red	Red
34	Blue	Blue		Blue	Blue	Blue
35	Green	Green		Green	Green	Green
36	Yellow	Yellow		Yellow	Yellow	Yellow
37	Tom is unpredicatble	no match		no match	Red	no match
38	Blue, green & Red has several keys	Blue, green, Red		Blue, Red	Green	Green
Sheet1

Cell Formulas
Range	Formula
D28:D38	D28	=CheckKeys(A28,C$28:C$31, FALSE)
E28:E38	E28	=IFERROR(LOOKUP(2,1/SEARCH(IF($C$28:$C$35<>"",$C$28:$C$35),A28),$C$28:$C$35),"no match")
F28:F38	F28	=IFERROR(LOOKUP(2,1/SEARCH(" "&$C$28:$C$35&" "," "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A28,"!",""),".",""),"?","")&" "),$C$28:$C$35),"no match")
B28:B38	B28	=CheckKeys(A28,C$28:C$31)

 

[topi1]

Check if any of the 2 options are what you need:

Option 1:

varios 29ene2024.xlsm
	A	B	C
27	Sample	Output	Key
28	Red wine on the carpet	Red	Red
29	Blue -like the color or the mood	Blue	Blue
30	yellow -testing if capitalization matters	Yellow	Yellow
31	xxgreenxx if the string needs to be isolated by whitespace or delimiters	Green	Green
32	Red	Red
33	Blue	Blue
34	Green	Green
35	Yellow	Yellow
sh3

Cell Formulas
Range	Formula
B28:B35	B28	=IFERROR(LOOKUP(2,1/SEARCH(IF($C$28:$C$35<>"",$C$28:$C$35),A28),$C$28:$C$35),"no match")
Press CTRL+SHIFT+ENTER to enter array formulas.

Option 2:

varios 29ene2024.xlsm
	A	B	C
27	Sample	Output	Key
28	Red wine on the carpet	Red	Red
29	Blue -like the color or the mood	Blue	Blue
30	yellow -testing if capitalization matters	Yellow	Yellow
31	xx green! if the string needs to be isolated by whitespace or delimiters	Green	Green
32	Red	Red
33	Blue	Blue
34	Green	Green
35	Yellow	Yellow
sh4

Cell Formulas
Range	Formula
B28:B35	B28	=IFERROR(LOOKUP(2,1/SEARCH(" "&$C$28:$C$35&" "," "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A28,"!",""),".",""),"?","")&" "),$C$28:$C$35),"no match")

@DanteAmor Thank you so much. I will likely be able to use both options. Work great.

 

[topi1]

Using Excel for text tasks quite often gives results that are less than perfect and you have to settle for the one that best suits your purpose at the time. So here is yet another option using vba to provide a user-defined function. Post back if you need implementation instructions.
Differences (could be good or bad?) with this one include.

1. Multiple Keys can be returned as in row 38 below. (Could be restricted to one if you wanted)
2. The upper/lower case of the result comes from the string being tested, not the Key list like the above formula options. (This could be altered with a bit more work.)
3. There must be no blank cells in the 'Key' range. (This could be allowed for with some more work)
4. This function readily handles more (but possibly not all) punctuation-like delimiters. One extra example below is the 'no-space hyphen' in row 30
5. The function can readily be made case-sensitive by including FALSE as the optional 3rd argument in the function as shown in column D
6. I was not sure about your intention/desire with the xxgreenxx example you gave but assume that you probably would not want that matched otherwise you could get a lot of matches with short words like "red" since it can be part of many longer words. I gave one extra example in row 37.

I have included @DanteAmor's suggestions in columns E & F so that you can easily see the various differences between all our suggestions & perhaps help choose which (if any) are best for your circumstances.

Function CheckKeys(s As String, rKeys As Range, Optional bIgnoreCase As Boolean = True) As String
  Dim RX As Object, m As Object
 
  Set RX = CreateObject("VBScript.RegExp")
  RX.Global = True
  RX.IgnoreCase = bIgnoreCase
  RX.Pattern = "\b(" & Join(Application.Transpose(rKeys), "|") & ")\b"
  For Each m In RX.Execute(s)
    CheckKeys = CheckKeys & ", " & m
  Next m
  If Len(CheckKeys) = 0 Then
    CheckKeys = "no match"
  Else
    CheckKeys = Mid(CheckKeys, 3)
  End If
End Function

topi1_2.xlsm
	A	B	C	D	E	F
27	Sample	Output	Key
28	Red wine on the carpet	Red	Red	Red	Red	Red
29	Blue -like the color or the mood	Blue	Blue	Blue	Blue	Blue
30	Blue-like the color or the mood	Blue	Yellow	Blue	Blue	no match
31	yellow -testing if capitalization matters	yellow	Green	no match	Yellow	Yellow
32	xxgreenxx if the string needs to be isolated by whitespace or delimiters	no match		no match	Green	no match
33	Red	Red		Red	Red	Red
34	Blue	Blue		Blue	Blue	Blue
35	Green	Green		Green	Green	Green
36	Yellow	Yellow		Yellow	Yellow	Yellow
37	Tom is unpredicatble	no match		no match	Red	no match
38	Blue, green & Red has several keys	Blue, green, Red		Blue, Red	Green	Green
Sheet1

Cell Formulas
Range	Formula
D28:D38	D28	=CheckKeys(A28,C$28:C$31, FALSE)
E28:E38	E28	=IFERROR(LOOKUP(2,1/SEARCH(IF($C$28:$C$35<>"",$C$28:$C$35),A28),$C$28:$C$35),"no match")
F28:F38	F28	=IFERROR(LOOKUP(2,1/SEARCH(" "&$C$28:$C$35&" "," "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A28,"!",""),".",""),"?","")&" "),$C$28:$C$35),"no match")
B28:B38	B28	=CheckKeys(A28,C$28:C$31)

@Peter_SSs I am using 2010 and could not use checkkeys but am planning to get 365 and will try. I like the idea of getting several keys. I could not find a way to run VBA. Thank you for all your time.

 

[topi1]

@DanteAmor I am hoping that if the string in the column A has multiple keys from column C, The formula in the column B shows the first key. I couldn't modify the formula to achieve that. Didn't realize that the original formula does that. Help please! Thank you in advance.

 

[topi1]

In the following example, why am I getting inconsistent results?In B28 and B32, I am getting second key. In B30 and B33 I get first key. If the formula yield only one of the keys present in the string, I always want the first key. TY.

Book1
	A	B	C
27	Sample	Output	Key
28	Red wine on the blue carpet	Blue	Red
29	Blue -like the color or the mood	Blue	Blue
30	yellow -testing if capitalization matters or red	Yellow	Yellow
31	xx green! if the string needs to be isolated by whitespace or delimiters	Green	Green
32	Red & blue	Blue
33	Blue & red	Blue
34	Green	Green
35	Yellow	Yellow
Sheet3

Cell Formulas
Range	Formula
B28:B35	B28	=IFERROR(LOOKUP(2,1/SEARCH(" "&$C$28:$C$35&" "," "&SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A28,"!",""),".",""),"?","")&" "),$C$28:$C$35),"no match")

 

[Peter_SSs]

@Peter_SSs I am using 2010 and could not use checkkeys but am planning to get 365 and will try. I like the idea of getting several keys. I could not find a way to run VBA. Thank you for all your time.

There should be no reason that you cannot use that udf with 2010. It does not require 365.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot in my previous post and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)

why am I getting inconsistent results?In B28 and B32,

It isn't inconsistent. In each case those formulas return the key that is furthest down the list if keys in column C.

I always want the first key

Just confirm whether you want the first key (left to right) found in the text in col A or the first key (top to bottom) from col C that is found in col A.

 

[topi1]

There should be no reason that you cannot use that udf with 2010. It does not require 365.
To implement ..
1. Right click the sheet name tab and choose "View Code".
2. In the Visual Basic window use the menu to Insert|Module
3. Copy and Paste the code below (you can use the icon at the top right of the code pane below) into the main right hand pane that opens at step 2.
4. Close the Visual Basic window.
5. Enter the formula as shown in the screen shot in my previous post and copy down.
6. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm)


It isn't inconsistent. In each case those formulas return the key that is furthest down the list if keys in column C.


Just confirm whether you want the first key (left to right) found in the text in col A or the first key (top to bottom) from col C that is found in col A.

@Peter_SSs I want the first key (left to right) found in the text in col A. The order of keys in the column C is not relevant to me. Contents of column C will change and are random. I want the formula in the cell Bn want to read the string in the cell An from left to right and yield the first word it finds that is present in the column c, regardless of what order it is in the column C and what follows that word in the rest of the string in An. Thak you so much continuing to help.

 

[topi1]

@Peter_SSs Made it work. Reviewing. B looks promising. It looks like it reads A from left to right. Need to strip the first word out. Will work on it and follow up. ty.