Quote Originally Posted by rao2016 View Post
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