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

[pma]

I pasted =LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10) into b2:b25. I then entered a different phrase including one of the colors into a26. When I hit enter the formula from b25 was copied down to b26, which was blank, automatically. I don't see any macros at work. What's going on here?

[barry houdini]

Hello pma, welcome to MrExcel.

This is an Excel feature (certainly in 2003), see this from Excel [2003] Help. See red highlighted section

Extend formats and formulas to additional rows

By default, Microsoft Excel automatically formats new data that you type at the end of a range to match the preceding rows. Excel also automatically copies formulas that have been repeated in the preceding rows and extends them to additional rows. Note In order to be extended to new rows in the range, the formats and formulas must appear in at least three of the five preceding rows. You can turn this option off (or back on again) at any time: On the Tools menu, click Options. On the Edit tab, clear the Extend data range formats and formulas check box to turn automatic formatting off. To turn automatic formatting back on again, select the Extend data range formats and formulas check box. ****** content=03/24/2005 name=MajorRevDate>****** content=08/15/2003 name=CreationDate>

[davejago]

Originally Posted by barry houdini

Here's one possibility.....

=LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10)

Can someone help me to break this formula out in layman's terms for me? I'm an intermediate excel user, but I don't understand how this is working.

[EugeneCarter]

Originally Posted by davejago

Can someone help me to break this formula out in layman's terms for me? I'm an intermediate excel user, but I don't understand how this is working.

Check out the posts from around Jun 17th, 2008 and the links within them. There were many posts on Barry's solution.

[Mr excel novice]

Originally Posted by barry houdini

Here's one possibility.....

=LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10)

Genius , but
-forgot one thing!
the challenge was for additional list items to be added without alteration to the function - keep your original with:

=LOOKUP(2^15,SEARCH(Keyword,A2),Assign)
where the defined names are:
Keyword =OFFSET(Sheet1!$D$1,1,0,(COUNTA(Sheet1!$D:$D)-1))
&
Assign =OFFSET(Sheet1!$E$1,1,0,(COUNTA(Sheet1!$E:$E))-1))

[Mr excel novice]

Originally Posted by Mr excel novice

Genius , but
-forgot one thing!
the challenge was for additional list items to be added without alteration to the function - keep your original with:

=LOOKUP(2^15,SEARCH(Keyword,A2),Assign)
where the defined names are:
Keyword =OFFSET(Sheet1!$D$1,1,0,(COUNTA(Sheet1!$D:$D)-1))
&
Assign =OFFSET(Sheet1!$E$1,1,0,(COUNTA(Sheet1!$E:$E))-1))

Sorry!
just noticed that this was covered before.
You could also change the 2^15 to COUNTA(Phrases), with dynamic range but this won't make the function any different.

[maheshmaxi]

Originally Posted by barry houdini

Here's one possibility.....

=LOOKUP(2^15,SEARCH(D$2:D$10,A2),E$2:E$10)

This is an excellent formula... Great Barry It helps me a lot...

[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)

Originally Posted by MrExcel

I've posted a new challenge of the month today. Like the last challenge, this one will have many different approaches. Post your entries here. While the ultimate "best" entry wins the LiveLessons DVD, several podcast DVD's are available to anyone who proposes a significant advancement towards a cool solution.

Entries are due by 15 July 2008.

Bill Jelen

[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)

[cornflakegirl]

schielrn - 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!

removing the &" " will mean that "redditch" becomes a problem again...