Function That Tests for 15 Different conditons?

[binar]

Fellow Forum Members,
Each cell in cells B1 through B15 contains text strings of various lengths.

Each of these text strings contains only one instance of the following target words:

Target1
Target2
Target3
Target4
Target5
Target6
Target7
Target8
Target9
Target10
Target11
Target12
Target13
Target14
Target15


I need help developing a function that will return values (words) on the neighboring A1 through A15 cells depending on what Target word it finds in cells B1 trhough B15. Below is a matrix that shows the conditions and associated response values:


If B1 contains the word "Target1" return word "Response1" in cell A1
If B2 contains the word "Target2" return word "Response2" in cell A2
If B3 contains the word "Target3" return word "Response3" in cell A3
If B4 contains the word "Target4" return word "Response4" in cell A4
If B5 contains the word "Target5" return word "Response5" in cell A5
If B6 contains the word "Target6" return word "Response6" in cell A6
If B7 contains the word "Target7" return word "Response7" in cell A7
If B8 contains the word "Target8" return word "Response8" in cell A8
If B9 contains the word "Target9" return word "Response9" in cell A9
If B10 contains the word "Target10" return word "Response10" in cell A10
If B11 contains the word "Target11" return word "Response11" in cell A11
If B12 contains the word "Target12" return word "Response12" in cell A12
If B13 contains the word "Target13" return word "Response13" in cell A13
If B14 contains the word "Target14" return word "Response14" in cell A14
If B15 contains the word "Target15" return word "Response15" in cell A15


Is such a function possible?

I've been playing around with the function below as a starting point. But I'm hitting a brick wall getting it to check for fifteen different scenarios:

=IF(NOT(ISERROR(FIND("Target1",A1))),"Response1","")

Any help will be greatly appreciated. Thanks.

 

[Desu Nota from Columbus]

Place in A1:

=IF(FIND("Target1",B1)>0,"Response1","")

Try this.


Is it possible that B1 could contain Target2 or Target10 or etc... and if so would you want A1 to return Response2 or Response10, respectively?

Ignore the question above ... T. Valko just did exactly what I was going to suggest if you answered yes.

 

[T. Valko]

Fellow Forum Members,
Each cell in cells B1 through B15 contains text strings of various lengths.

Each of these text strings contains only one instance of the following target words:

Target1
Target2
Target3
Target4
Target5
Target6
Target7
Target8
Target9
Target10
Target11
Target12
Target13
Target14
Target15


I need help developing a function that will return values (words) on the neighboring A1 through A15 cells depending on what Target word it finds in cells B1 trhough B15. Below is a matrix that shows the conditions and associated response values:


If B1 contains the word "Target1" return word "Response1" in cell A1
If B2 contains the word "Target2" return word "Response2" in cell A2
If B3 contains the word "Target3" return word "Response3" in cell A3
If B4 contains the word "Target4" return word "Response4" in cell A4
If B5 contains the word "Target5" return word "Response5" in cell A5
If B6 contains the word "Target6" return word "Response6" in cell A6
If B7 contains the word "Target7" return word "Response7" in cell A7
If B8 contains the word "Target8" return word "Response8" in cell A8
If B9 contains the word "Target9" return word "Response9" in cell A9
If B10 contains the word "Target10" return word "Response10" in cell A10
If B11 contains the word "Target11" return word "Response11" in cell A11
If B12 contains the word "Target12" return word "Response12" in cell A12
If B13 contains the word "Target13" return word "Response13" in cell A13
If B14 contains the word "Target14" return word "Response14" in cell A14
If B15 contains the word "Target15" return word "Response15" in cell A15


Is such a function possible?

I've been playing around with the function below as a starting point. But I'm hitting a brick wall getting it to check for fifteen different scenarios:

=IF(NOT(ISERROR(FIND("Target1",A1))),"Response1","")

Any help will be greatly appreciated. Thanks.

See if this does what you want.

Create a 2 column table like this:

Book1
	F	G
1	Target1	Response1
2	Target2	Response2
3	Target3	Response3
4	Target4	Response4
5	Target5	Response5
6	Target6	Response6
7	Target7	Response7
8	Target8	Response8
9	Target9	Response9
10	Target10	Response10
11	Target11	Response11
12	Target12	Response12
13	Target13	Response13
14	Target14	Response14
15	Target15	Response15
Sheet1

Then:

Book1
	B	C
1	This is Target12	Response12
2	Target15 is here	Response15
3	Giant Target7 stuff	Response7
Sheet1

This formula entered in C1 and copied down:

=LOOKUP(1E100,SEARCH(F$1:F$15,B1),G$1:G$15)

 

[Desu Nota from Columbus]

T.Valko,


I have seen this type of lookup many times, and I know how to use it but I don't understand it.

Why does it work? Why doesn't excel return #N/A when it doesn't find that huge number?

 

[T. Valko]

T.Valko,


I have seen this type of lookup many times, and I know how to use it but I don't understand it.

Why does it work? Why doesn't excel return #N/A when it doesn't find that huge number?

The secret is that the formula will "find" the LAST number that is less than or equal to that big number.

The SEARCH function will return either an error or a number from 1 to the maximum number of characters allowed in a cell which is 32.767. This number is the starting position of the lookup value within the string.

Let's see how this works using this smaller data sample.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 79px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">Target1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Response1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Target2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Response2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Target3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Response3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Target4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Response4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Target5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Response5</TD></TR></TBODY></TABLE>


B1 = This is Target2

=LOOKUP(1E100,SEARCH(F$1:F$5,B1),G$1:G$5)

Returns: Response2

We search for each of the "target" values in the cell.

SEARCH(F$1:F$5,B1)

If a particular target value is found then SEARCH returns its starting position in the string. This position number will be from 1 to 32.767. If a particular target value is not found then SEARCH will return a #VALUE! error.

Based on the sample data the results of SEARCH(F$1:F$5,B1) will be:

Target1 = #VALUE!
Target2 = 9
Target3 = #VALUE!
Target4 = #VALUE!
Target5 = #VALUE!

The way that LOOKUP works in this application is that it will find the LAST number from the results of the SEARCH array:
{#VALUE!;9;#VALUE!;#VALUE!;#VALUE!}

That is less than or equal to the big number lookup value 1E100.

In this case there is only one number in the array so by default it's the LAST number in the array that is less than or equal to 1E100.

So, we want the result of the formula to come from the cell in G1:G5 that corresponds to the 9 in the SEARCH array.

Sheet1

<TABLE style="PADDING-RIGHT: 2pt; PADDING-LEFT: 2pt; FONT-SIZE: 10pt; FONT-FAMILY: Verdana,Arial; BACKGROUND-COLOR: #ffffff" cellSpacing=0 cellPadding=0 border=0><COLGROUP><COL style="FONT-WEIGHT: bold; WIDTH: 30px"><COL style="WIDTH: 72px"><COL style="WIDTH: 79px"></COLGROUP><TBODY><TR style="FONT-WEIGHT: bold; FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center"><TD> </TD><TD>F</TD><TD>G</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">1</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-BOTTOM: #000000 1px solid">#VALUE!</TD><TD style="BORDER-RIGHT: #000000 1px solid; BORDER-TOP: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Response1</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">2</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid; TEXT-ALIGN: center">9</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Response2</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">3</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">#VALUE!</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Response3</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">4</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">#VALUE!</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Response4</TD></TR><TR style="HEIGHT: 17px"><TD style="FONT-SIZE: 8pt; BACKGROUND-COLOR: #cacaca; TEXT-ALIGN: center">5</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT: #000000 1px solid; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">#VALUE!</TD><TD style="BORDER-TOP-WIDTH: 1px; BORDER-RIGHT: #000000 1px solid; BORDER-LEFT-WIDTH: 1px; BORDER-LEFT-COLOR: #000000; BORDER-TOP-COLOR: #000000; BORDER-BOTTOM: #000000 1px solid">Response5</TD></TR></TBODY></TABLE>


So...

=LOOKUP(1E100,SEARCH(F$1:F$5,B1),G$1:G$5)

Returns: Response2

On a side note...

I use the lookup value 1E100 because it's GUARANTEED to be larger than the largest possible number that SEARCH can return and, more importantly, it's a lot easier to read/write/remember than that number you're used to seeing here. A million 9's or something or other!

 

[binar]

T. Valko,
Thanks a lot for your post. I replaced the dummy data with the Data I'm using and glad to report to you that it works perfectly. The beauty about it is that I have the flexibility to add more conditions in the future.

The one thing I found a mystery to your equation is the "1E100". What does it do? Why is there? However, it seems that you have already addressed this mystery with your most recent post.

In addition, I noticed that when I pasted your function into Excel the 1E100 automatically changed to "1E+100" I was curious to know why Excel automatically adds a PLUS symbol.

Again, thanks for you help. I really appreciate it a lot.

 

[T. Valko]

T. Valko,
Thanks a lot for your post. I replaced the dummy data with the Data I'm using and glad to report to you that it works perfectly. The beauty about it is that I have the flexibility to add more conditions in the future.

The one thing I found a mystery to your equation is the "1E100". What does it do? Why is there? However, it seems that you have already addressed this mystery with your most recent post.

In addition, I noticed that when I pasted your function into Excel the 1E100 automatically changed to "1E+100" I was curious to know why Excel automatically adds a PLUS symbol.

Again, thanks for you help. I really appreciate it a lot.

1E100 is scientific notation, "shorthand", for the very, very, very large number: 1 followed by 100 zeros.

As I noted in my other reply, the SEARCH function can only return a number to the order of 32,767. So, 1E100 is guaranteed to be larger than 32,767 so the formula works as expected.

Excel converts the string 1E100 to 1E+100 just as a formatting style.

 

[binar]

1E100 is scientific notation, "shorthand", for the very, very, very large number: 1 followed by 100 zeros.

As I noted in my other reply, the SEARCH function can only return a number to the order of 32,767. So, 1E100 is guaranteed to be larger than 32,767 so the formula works as expected.

Excel converts the string 1E100 to 1E+100 just as a formatting style.

T. Valko,
I'm wondering if you would be kind enough to perform a small test with your computer and see if you get the same error I get.

Please duplicate the following:

F1 = OPEN
F2 = PEN
G1 = Return the word OPEN
G2 = Return the word PEN


B1 = OPEN THE DOOR
B2 = USE A PEN


C1 input =LOOKUP(1E+100,SEARCH(F$1:F$2,B1),G$1:G$2)
C2 input =LOOKUP(1E+100,SEARCH(F$1:F$2,B2),G$1:G$2)


The error I get are in cells C1 and C2. Both C1 and C2 cells show the same text string:
"Return the word PEN"

C1 should show the text string "Return the word OPEN'
C2 should show the text string "Return the word PEN"

Do you have any idea why Excel is producing this error?
The words OPEN and PEN are dummy data.
However, I'm getting the same result with the real data which is PIM and OPIM.

For some reason Excel is confusing the data and giving wrong results. Is there a setting in Excel that needs to be calibrated or changed so that I can get correct results? Any help will be greatly appreciated. Thanks.

 

[T. Valko]

T. Valko,
I'm wondering if you would be kind enough to perform a small test with your computer and see if you get the same error I get.

Please duplicate the following:

F1 = OPEN
F2 = PEN
G1 = Return the word OPEN
G2 = Return the word PEN


B1 = OPEN THE DOOR
B2 = USE A PEN


C1 input =LOOKUP(1E+100,SEARCH(F$1:F$2,B1),G$1:G$2)
C2 input =LOOKUP(1E+100,SEARCH(F$1:F$2,B2),G$1:G$2)


The error I get are in cells C1 and C2. Both C1 and C2 cells show the same text string:
"Return the word PEN"

C1 should show the text string "Return the word OPEN'
C2 should show the text string "Return the word PEN"

Do you have any idea why Excel is producing this error?
The words OPEN and PEN are dummy data.
However, I'm getting the same result with the real data which is PIM and OPIM.

For some reason Excel is confusing the data and giving wrong results. Is there a setting in Excel that needs to be calibrated or changed so that I can get correct results? Any help will be greatly appreciated. Thanks.

This is not an error.

This is expected behavior.

There are multiple substring matches in a cell so the formula returns the match that is closest to the bottom of the lookup table.

We can reduce the chance of getting "false positives" like this:

=LOOKUP(1E100,SEARCH(" "&F$1:F$2&" "," "&B1&" "),G$1:G$2)

What we're doing with this version is we're looking for the lookup value with space characters on each side.

So, instead of looking for PEN which will return a false positive when compared to OPEN, we look for _PEN_ (underscores represent space characters) in the lookup table where we've also padded each entry with "virtual" space characters.

 

[binar]

This is not an error.

This is expected behavior.

There are multiple substring matches in a cell so the formula returns the match that is closest to the bottom of the lookup table.

We can reduce the chance of getting "false positives" like this:

=LOOKUP(1E100,SEARCH(" "&F$1:F$2&" "," "&B1&" "),G$1:G$2)

What we're doing with this version is we're looking for the lookup value with space characters on each side.

So, instead of looking for PEN which will return a false positive when compared to OPEN, we look for _PEN_ (underscores represent space characters) in the lookup table where we've also padded each entry with "virtual" space characters.

T. Valko,
Thanks for the explanation. Never was aware that Excel could produce what you call False Positives.

Your padding with space doesn't apply very well to my data.

My data follows this format:

OPEN-###-####
PEN-###-####

In other words, there is no blank spaces to the left or right. There is only a hypen on the right (as shown). I would be very grateful if you can provide a suggestion that may be a better match for the format of my data. Thanks.