Compare a small string in big string in excel

[johnmark123]

I have to compare a small tring in a string. For example, I have two columns that are A and B

     A            B
    test_BL_pa.txt     1
    test_AL_pa.txt     2 
    test_BL_pa.txt     3 
    test_CL_pa.txt     4

With input are test_BL,test_AL,test_CL. I want to get the value at column B corresponding with column A . Hence, given
Output:

        Input           Value
       test_BL          4(1+3)
       test_AL          2
       test_CL          4

Could you help me for implementation it by excel

 

[Marcelo Branco]

Hi and welcome to Mr Excel forum

Maybe

	A​	B​	C​	D​	E​
1​	test_BL_pa.txt​	1​		Input​	Value​
2​	test_AL_pa.txt​	2​		test_BL​	4​
3​	test_BL_pa.txt​	3​		test_AL​	2​
4​	test_CL_pa.txt​	4​		test_CL​	4​

Formula in E2 copied down
=SUMIF(A:A,D2&"*",B:B)

Hope this helps

M.

 

[johnmark123]

Thank you so much. I got it

Hi and welcome to Mr Excel forum

Maybe

	A​	B​	C​	D​	E​
1​	test_BL_pa.txt​	1​		Input​	Value​
2​	test_AL_pa.txt​	2​		test_BL​	4​
3​	test_BL_pa.txt​	3​		test_AL​	2​
4​	test_CL_pa.txt​	4​		test_CL​	4​

<tbody>
</tbody>

Formula in E2 copied down
=SUMIF(A:A,D2&"*",B:B)

Hope this helps

M.

 

[johnmark123]

Thank Marcelo Branco. Your function works well. How about the other problem. Given the values of column D and E. I want to fill the value of column E into column B, corresponding with string of column D is same with string of column A

	A​	B​	C​	D​	E​
1​	test_BL_pa.txt	4​		Input	Value
2​	test_AL_pa.txt	2​		BL_pa_txt	4​
3​	test_BL_pa.txt	4​		AL_pa.txt	2​
4​	test_CL_pa.txt	4​		CL_pa.txt	4​

<tbody>
</tbody>

 

[Marcelo Branco]

Thank Marcelo Branco. Your function works well. How about the other problem. Given the values of column D and E. I want to fill the value of column E into column B, corresponding with string of column D is same with string of column A

	A​	B​	C​	D​	E​
1​	test_BL_pa.txt	4​		Input	Value
2​	test_AL_pa.txt	2​		BL_pa_txt	4​
3​	test_BL_pa.txt	4​		AL_pa.txt	2​
4​	test_CL_pa.txt	4​		CL_pa.txt	4​

<TBODY>
</TBODY>

Not sure if I understood correctly what you need.

Maybe...

	A​	B​	C​	D​	E​
1​	test_BL_pa.txt​	4​		Input​	Value​
2​	test_AL_pa.txt​	2​		BL_pa.txt​	4​
3​	test_BL_pa.txt​	4​		AL_pa.txt​	2​
4​	test_CL_pa.txt​	4​		CL_pa.txt​	4​

Formula in B1 copied down
=LOOKUP(9.99E+307,SEARCH($D$2:$D$4,A1),$E$2:$E$4)

M.

 

[johnmark123]

It is correct. Thank you so much

Not sure if I understood correctly what you need.

Maybe...

	A​	B​	C​	D​	E​
1​	test_BL_pa.txt​	4​		Input​	Value​
2​	test_AL_pa.txt​	2​		BL_pa.txt​	4​
3​	test_BL_pa.txt​	4​		AL_pa.txt​	2​
4​	test_CL_pa.txt​	4​		CL_pa.txt​	4​

<tbody>
</tbody>

Formula in B1 copied down
=LOOKUP(9.99E+307,SEARCH($D$2:$D$4,A1),$E$2:$E$4)

M.