***WINNERS ANNOUNCEMENT*** June/July 2008 Challenge of the Month

[jonmo1]

there is a string of ascii characters at the end of each cell - a bunch of 160s (not sure what this is) ending with a 32 (space). i think mr excel is messing with our minds!

Hmm, LOL. I thought maybe it was an accident or problem with the download of the file...

In that case, here's an update to my UDF to account for that...

Public Function Jonmo1(C As Range, L As Range)
Dim MyArray As Variant
Dim X As Long, Y
Dim MyString As String
MyArray = Split(C, " ")
Jonmo1 = ""
For X = LBound(MyArray) To UBound(MyArray)
    MyString = Replace(MyArray(X), Chr(160), "")
    Y = Application.Match(MyString, L.Columns(1), 0)
    If Not IsError(Y) Then
        Jonmo1 = L(Y, 2)
        Exit For
    End If
Next X
End Function

[Aladin Akyurek]

Originally Posted by schielrn

Aladin,

I could only get your formula to work for the phrases that did not end with the color. The only way I could get it to work in all cases if the phrase ends with the color was by removing the &" "

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$D$2:$D$10," "&A2),$E$2:$E$10)

Given Bill's data...

Phrases	Assignments		Keyword	Assigned to
the ocean is blue	Joe		blue	Joe
I like the freddy shirt	#N/A		red	Bob
blue is my favorite color	Joe		yellow	Mary
the ocean is red	Bob		pink	Fred
I like his red shirt	Bob		orange	Ralph
red is my favorite color	Bob		brown	Lora
the ocean is yellow	Mary		white	Tracy
I like his yellow shirt	Mary		lavendar	Earl
yellow is my favorite color	Mary		magenta	Jenny
the ocean is pink	Fred
I like his pink shirt	Fred
	#N/A

=LOOKUP(9.99999999999999E+307,SEARCH(" "&$D$2:$D$10&" "," "&A2&" "),$E$2:$E$10)

should simply work.

[cornflakegirl]

Aladin - did you see my post - the downloaded file isn't quite what it looks like...

I think

=IF(ISNA(LOOKUP(2^15,SEARCH(" "&$D$2:$D$10&" "," "&A2&" "),$E$2:$E$10)),LOOKUP(2^15,SEARCH(" "&$D$2:$D$10&CHAR(160)," "&A2&" "),$E$2:$E$10),LOOKUP(2^15,SEARCH(" "&$D$2:$D$10&" "," "&A2&" "),$E$2:$E$10))

will work - but it's nasty!

[schielrn]

Ok thanks for the explanation, I see that now.

[Aladin Akyurek]

Originally Posted by cornflakegirl

Aladin - did you see my post - the downloaded file isn't quite what it looks like...

I think

=IF(ISNA(LOOKUP(2^15,SEARCH(" "&$D$2:$D$10&" "," "&A2&" "),$E$2:$E$10)),LOOKUP(2^15,SEARCH(" "&$D$2:$D$10&CHAR(160)," "&A2&" "),$E$2:$E$10),LOOKUP(2^15,SEARCH(" "&$D$2:$D$10&" "," "&A2&" "),$E$2:$E$10))

will work - but it's nasty!

Emma,

If it's the case that A2 has a bunch of CHAR(160), it is simpler to invoke:

=LOOKUP(9.99999999999999E+307,
   SEARCH(" "&$D$2:$D$10&" "," "&SUBSTITUTE(A2,CHAR(160),"")&" "),
   $E$2:$E$10)

[arkusM]

Aladin,
I am running XL2003 and have no issue with the first posted formula. It works perfectly.

M

Originally Posted by Aladin Akyurek

You'd need to invoke a formula of the type as in:

http://www.mrexcel.com/forum/showthread.php?t=321942

http://www.mrexcel.com/forum/showthread.php?t=323188


Applied to the problem/task at hand...

=LOOKUP(9.99999999999999E+307,
    SEARCH(" "&$D$2:$D$10&" "," "&A2&" "),
    $E$2:$E$10)

On Excel 2003 and beyond, convert D2:E10 into a list (Data|List|CreateList).

On earlier version, we would need define dynamic named ranges, say, KEYS and SLOTS, and invoke:

=LOOKUP(9.99999999999999E+307,
    SEARCH(" "&KEYS&" "," "&A2&" "),
    SLOTS)

[tjmcgough]

Hi everyone,

Was happy to see my question posted as the challenge. I'm a new user to this forum and appreciate all the responses.

Richard, have tried your solution and it solves most of the issue. However, i noticed a few limitations still exist;

1. If no keyword match is found the first result is still returned. Anyway to have a blank returned if no match found?
2. If a new row is added to the keyword table, the entire formula "breaks". Was thinking of adding placeholder keywords that would never match to allow for future growth of new rows. Example, adding a bunch of rows with ">>>Unused For Now<<<", and then changing to the "real" keyword if/when needed.

Any ideas on how to fix one or both?

Many thanks,

Tom

[RichardSchollar]

Hi Tom

i think the answer to both is to use Barry's formula (possibly with Aladin's modification).

[rgdwar1]

=index($e$2:$e$10,match(1,search("*"&$d$2:$d$10&"*",a2),0))

Ctrl-shift-enter

[rgdwar1]

Also, I realized that if no phrase was entered or one was accidentally deleted, I didn't have any code to catch this so I added the following:

=IF(A2="","No Phrase Entered",INDEX($E$2:$E$10,MATCH(1,SEARCH("*"&$D$2:$D$10&"*",A2),0)))

confirmed with CTRL-SHIFT-ENTER