Excel: Text with * in formulas 2

[Larft]

I am working with a simple IF/OR formula in Excel that I want to return one of several predetermined values based on the content in the reference cell. The data in the cell is text and I am trying to use a wildcard to match the criteria so that if just part of the text string is in the cell it will be a match.

Here is the code:

=IF(OR(OR(F3="text1"),OR(F3="text2")),LOOKUP(F3,{"text1","text2"},{"35-40","28-32"}),"30-35")

This works if the value is matched exactly, and if I surround the value with wildcards like this "*text1*" it then only matches the value *text1*. This is easy in Access where the statement:

Like "*" &[text1] & "*"

Will return any permutation of text1

How do I do this in Excel?

Thanks in advance for your help.

 

[SkipVought]

Hi,

Use the FIND function and ISNA to determin not found.

Skip,
[sub]
[red][/red]
[/sub]

 

[Larft]

Hi Skip, thanks for the reply.

I should have mentioned that this functions correctly if the values to match are not found in the cell. The purpose of this is when the user enters a text value in the reference cell and it matches one of the two predetermined criteria it will return one of two predetermined results, otherwise it returns a third value for everything else. The everything else part works fine, but I want the formula to match any occurrence of text1 or text2 inside the text string, such that ABCDtext1DEF will return a match to text1, hence my attempts to use * in the formula to no avail.

I don't see how FIND and ISNA helps in this situation since I'm not getting the match to the text variable and the second result for IF works.

Thanks in advance for your help

 

[AnotherHiggins]

A bit off topic, but your formula seems overly complex. There is no reason to include the stuff in red:

=IF(OR([red]OR([/red]F3="text1"[red])[/red],[red]OR([/red]F3="text2"[red])[/red]),LOOKUP(F3,{"text1","text2"},{"35-40","28-32"}),"30-35")

But to see if Text1 exists somewhere within a cell, follow Skip's advice and use FIND or SEARCH. (Note: Find is case-sensitive, search is not.)

Example:

=if(isnumber(search("text1", F3)), "35-40", if(isnumber(search("text2", F3)), "28-32", "30-35"))

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Larft]

Thanks Skip and anotherhiggins,

Skips first suggestion was clarifed by anotherhiggins second, I now see the error of my ways...

The search function did the trick.

Thanks to both of you!

 

[AnotherHiggins]

Glad you got it sorted out!


[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.