specific text in a cell should return a text in another cell

Hi there. You can use this:
=IF(ISNUMBER(SEARCH("S1",A1)),"Stage 1",IF(ISNUMBER(SEARCH("S2",A1)),"Stage 2",""))

Formula in B1 and copied down

Formula:

Please Login or Register  to view this content.

v	A	B
1	A1B1-S1C1	Stage 1
2	A1B1-S2C1	Stage 2

Here's another one...

=IF(COUNTIF(A2,"*S1*"),"Stage 1",IF(COUNTIF(A2,"*S2*"),"Stage 2",""))

If you have many codes you could create a table like this...

Data Range

	A	B	C	D	E
1	------	------	------	------	------
2	A1B1-S1C1	Stage 1		S1	Stage 1
3	A1B1-S2C1			S3	Stage 3
4	A1B1-S3C1	Stage 3		S4	Stage 4
5	A1B1-S4C1	Stage 4		S7	Stage 7
6	A1B1-S5C1
7

This formula entered in B2 and copied down:

=IFERROR(LOOKUP(1E100,SEARCH(D$2:D$5,A2),E$2:E$5),"")

If all of your data are as like as ****-S***, then you can use this formula also :

Please Login or Register  to view this content.

But this formula will work for 1 digit stage only (1-9).

If all of your data are as like as ****-S**C**, then you can use this formula :

Please Login or Register  to view this content.

This will work for any digit.

Originally Posted by rao2016

Can you explain what is the meaning of 1E+100 and other contained in the brackets

Here's how that works...

Data Range

	A	B	C	D	E
1	------	------	------	------	------
2	A1B1-S1C1	Stage 1		S1	Stage 1
3				S3	Stage 3
4				S4	Stage 4
5				S7	Stage 7
6

This formula entered in B2:

=IFERROR(LOOKUP(1E100,SEARCH(D$2:D$5,A2),E$2:E$5),"")

This expression searches for each of the substrings in D2:D5 within the larger string in A2:

SEARCH(D$2:D$5,A2)

If the substring is found the SEARCH function returns the starting position number of the substring. This will be a number from 1 to 32767 (the max number of characters allowed in a cell). If the substring is not found the SEARCH function returns a #VALUE! error:

SEARCH("S1","A1B1-S1C1") = 6
SEARCH("S3","A1B1-S1C1") = #VALUE!
SEARCH("S4","A1B1-S1C1") = #VALUE!
SEARCH("S7","A1B1-S1C1") = #VALUE!

SEARCH("S1","A1B1-S1C1") = 6 - The substring "S1" was found starting at character number 6 within the larger string.

Now we have this array:

{6;#VALUE!;#VALUE!;#VALUE!}

=IFERROR(LOOKUP(1E+100,{6;#VALUE!;#VALUE!;#VALUE!},E$2:E$5),"")

1E+100 is scientific notation for the very large number 1 followed by 100 0s. Scientific notation is a "shorthand" method of expressing very long numbers.

The way that LOOKUP works is if the lookup value of 1E+100 is larger than any other number within the array then LOOKUP will match the last number in the array.

The last number in the array is 6 (it's also the only number in the array).

LOOKUP will return the value in E2:E5 that corresponds to the 6 in the array:

6: E2
#VALUE!: E3
#VALUE!: E4
#VALUE!: E5

E2 = String 1

LOOKUP(1E+100,SEARCH(D$2:D$5,A2),E$2:E$5) = String 1.

If none of the substrings in D2:D5 are found then the array would be all #VALUE! errors and the result of the LOOKUP function would be #VALUE!.

We use the IFERROR function to trap any errors and return a blank "" instead.

So:

=IFERROR(LOOKUP(1E+100,SEARCH(D$2:D$5,A2),E$2:E$5),"")

= String 1

You're welcome. Thanks for the feedback!