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
Bookmarks