VLOOKUP that returns a substring (defined by specific delimiters)that contains specific characters

[dagda13]

Hi,

I've been trying figure out (preferably without INDEX or arrays...my computer is too slow to handle them when dealing with hundreds of rows) a way to perform a VLOOKUP that will return a sub-string (said sub-string defined by the "|" delimiter) that contains specific characters. These sub-strings could be anywhere (no fixed position) in the string.

For example, if I perform a VLOOKUP and want to return a sub-string (with "|" delimiters) that contains "_ptbr", I would want to get "ds_box17_01_ptbr" because it is within the "|" delimiter and contains the specific "_ptbr" characters:

Lookup Value	String containing Sub-String	Desired Output
ds_box17_01_enus	|ds_box17_01_dede|ds_box17_01_zhcn|ds_box17_01_frfr|ds_box17_01_ptbr|ds_box17_01_es|	ds_box17_01_ptbr

Is there a way to do this? Any help very much appreciated!

 

[aRandomHelper]

Probably not the best formula out there, but hope this helps:

Book1.xlsm
	A	B	C
1	Lookup Value	String containing Sub-String	Desired Output
2	ds_box17_01_enus	|ds_box17_01_dede|ds_box17_01_zhcn|ds_box17_01_frfr|ds_box17_01_ptbr|ds_box17_01_es|	ds_box17_01_ptbr
3		|ds_box17_01_dede|ds_box17_01_zhcn|ds_box17123_01_ptbr|ds_box17_01_es|	ds_box17123_01_ptbr
4		1st_ds_box17_01_ptbr|ds_box17_01_es|	1st_ds_box17_01_ptbr
5		|ds_box17_01_dede|abcde|ds_box17_01_frfr|last_ds_box17_01_ptbr	last_ds_box17_01_ptbr
Sheet7

Cell Formulas
Range	Formula
C2:C5	C2	=IF(ISERR(FIND("_ptbr",MID(IFERROR(LEFT(B2,FIND("|",B2,FIND("_ptbr",B2))-1),B2),IFERROR(FIND("||",SUBSTITUTE(B2,"|","||",LEN(IFERROR(LEFT(B2,FIND("|",B2,FIND("_ptbr",B2))-1),B2))-LEN(SUBSTITUTE(IFERROR(LEFT(B2,FIND("|",B2,FIND("_ptbr",B2))-1),B2),"|",""))))+1,1),999))),"",MID(IFERROR(LEFT(B2,FIND("|",B2,FIND("_ptbr",B2))-1),B2),IFERROR(FIND("||",SUBSTITUTE(B2,"|","||",LEN(IFERROR(LEFT(B2,FIND("|",B2,FIND("_ptbr",B2))-1),B2))-LEN(SUBSTITUTE(IFERROR(LEFT(B2,FIND("|",B2,FIND("_ptbr",B2))-1),B2),"|",""))))+1,1),999))

Keep your Vlookup formula in column B, and place this formula in column C.

 

[Peter_SSs]

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

I'm also wondering if you have given us a big enough sample to fully understand what you have and what you are trying to achieve. Could you give us a slightly bigger sample, including the expected result(s), with XL2BB?

Make sure your sample(s) show us what sort of variety there might be in the original data and the expected results.

 

[jtakw]

Hi,

If in case the samples provided by @aRandomHelper is representative of your data, here's another formula to consider:

Book3.xlsx
	A	B	C
1	Lookup Value	String containing Sub-String	Desired Output
2	ds_box17_01_enus	|ds_box17_01_dede|ds_box17_01_zhcn|ds_box17_01_frfr|ds_box17_01_ptbr|ds_box17_01_es|	ds_box17_01_ptbr
3		|ds_box17_01_dede|ds_box17_01_zhcn|ds_box17123_01_ptbr|ds_box17_01_es|	ds_box17123_01_ptbr
4		1st_ds_box17_01_ptbr|ds_box17_01_es|	1st_ds_box17_01_ptbr
5		|ds_box17_01_dede|abcde|ds_box17_01_frfr|last_ds_box17_01_ptbr	last_ds_box17_01_ptbr
Sheet909

Cell Formulas
Range	Formula
C2:C5	C2	=TRIM(RIGHT(SUBSTITUTE(LEFT(B2,FIND("_ptbr",B2)+4),"|",REPT(" ",99)),99))