Search text and display required information

PRADEEPSWALSE

New Member
Joined
Nov 27, 2018
Messages
14
Hello,

I am attempting to use a formula that will search for key words in text fields of cells and return the appropriate category.
=LOOKUP(9.99999999999999E+307,SEARCH(" "&MOC&" "," "&$C2),CODE)

But it returns to #N/A.

PIPE SIZE, 20NB, SCH STD, ASTM A106 GR.B, PE, SEAMLESS, B 36.10M

<tbody>
</tbody>
#N/A

<tbody>
</tbody>
PIPE SIZE, 15NB, SCH STD, ASTM A312 TP304, PE, SEAMLESS, B 36.10M

<tbody>
</tbody>
#N/A

<tbody>
</tbody>
PIPE SIZE, 15NB, SCH XS, ASTM A333 GR.6, PE, SEAMLESS, B 36.10M

<tbody>
</tbody>
#N/A

<tbody>
</tbody>
PIPE SIZE, 15NB, SCH XXS, ASTM A106 GR.B, PE, SEAMLESS, B 36.10M

<tbody>
</tbody>
#N/A

<tbody>
</tbody>

<tbody>
</tbody>














MOC

<tbody>
</tbody>
CODE

<tbody>
</tbody>
ASTM A106 GR.B

<tbody>
</tbody>
C01
ASTM A333 GR.6

<tbody>
</tbody>
L01
ASTM A312 TP304

<tbody>
</tbody>
S01

<tbody>
</tbody>











Please help.
 

PRADEEPSWALSE

New Member
Joined
Nov 27, 2018
Messages
14
Hello Sir,

This is short word fixed by me for some material. I want use this short word (i.e. C01, L01 etc.) to other place.
If cell contains text "ASTM A106 GR.B" then in next cell shows "C01" and if cell contains text "ASTM A333 GR.6" then in next cell shows "L01" and so on... I have more than 100 cases.
 

PRADEEPSWALSE

New Member
Joined
Nov 27, 2018
Messages
14
Try

=LOOKUP(9.99999999999999E+307,SEARCH(MOC,$C2),CODE)

M.

Thanks for the solution. It is working fine and I getting desired results.

Can you please explain that how this formulae is working?

This will help me in future, if required.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,340
You're welcome.

The formula i suggested above is almost identical to yours. The only difference is that it doesn't concatenate a space before and after MOC. Observe that in the long text the instances are followed by a comma, not a space - that's is the reason why your original formula wasn't working.

M.
 

PRADEEPSWALSE

New Member
Joined
Nov 27, 2018
Messages
14
Hello,

Okay.

Now I am facing another problem.


MOCCODE
ASTM A420 GR.WPL6L02
ASTM A420 GR.WPL6-WL03

<tbody>
</tbody>
In above table, MOC column contains different text with minor difference i.e "-W" is available in second row.

Refer below table, in first case result shows "#N/A" and for second case correct result is appearing.


90 DEG. LR ELBOW, SCH 80, BW, ASTM A420 GR.WPL6#N/A
90 DEG. LR ELBOW, SCH 80, BW, ASTM A420 GR.WPL6-WL03

<tbody>
</tbody>

Please suggest how resolve this problem with modification of same formula. I have lot of same cases in my excel file.

Regards,
Pradeep
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,340
The formula worked for me


A
B
C
D
E
1
Text​
Result​
MOC​
CODE​
2
90 DEG. LR ELBOW, SCH 80, BW, ASTM A420 GR.WPL6​
L02​
ASTM A420 GR.WPL6​
L02​
3
90 DEG. LR ELBOW, SCH 80, BW, ASTM A420 GR.WPL6-W​
L03​
ASTM A420 GR.WPL6-W​
L03​
4

Formula in B2 copied down
=LOOKUP(9.99999999999999E+307,SEARCH(MOC,$A2),CODE)

M.
 

Marcelo Branco

MrExcel MVP
Joined
Aug 23, 2010
Messages
16,340
A more robust formula

=LOOKUP(9.99999999999999E+307,SEARCH(" "&MOC&" "," "&SUBSTITUTE($A2,","," ")&" "),CODE)

M.
 

Forum statistics

Threads
1,085,210
Messages
5,382,369
Members
401,784
Latest member
Jonnyboisme

Some videos you may like

This Week's Hot Topics

Top