How to use trim function

star7576

New Member
Joined
Dec 8, 2009
Messages
6
<o:p> </o:p>Table 2
<o:p> </o:p>
<table class="MsoTableGrid" style="border: medium none ; border-collapse: collapse; margin-left: 6.75pt; margin-right: 6.75pt;" align="left" border="1" cellpadding="0" cellspacing="0"> <tbody><tr style=""> <td style="border: 1pt solid black; padding: 0in 5.4pt; background: rgb(141, 179, 226) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" valign="top"> <o:p> </o:p>
</td> <td style="border-style: solid solid solid none; border-color: black black black -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0in 5.4pt; background: rgb(141, 179, 226) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" valign="top"> A<o:p></o:p>
</td> <td style="border-style: solid solid solid none; border-color: black black black -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0in 5.4pt; background: rgb(141, 179, 226) none repeat scroll 0% 0%; width: 79.85pt; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="106" valign="top"> B<o:p></o:p>
</td> <td style="border-style: solid solid solid none; border-color: black black black -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0in 5.4pt; background: rgb(141, 179, 226) none repeat scroll 0% 0%; width: 58.5pt; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous;" width="78" valign="top"> C<o:p></o:p>
</td> </tr> <tr style=""> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt;" valign="top"> 1<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt;" valign="top"> Product codes<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 79.85pt;" width="106" valign="top"> Trim here???<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt;" width="78" valign="top"> price<o:p></o:p>
</td> </tr> <tr style=""> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt;" valign="top"> 2<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt;" valign="top"> AOC115<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 79.85pt;" width="106" valign="top"> <o:p> </o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt;" width="78" valign="top"> <o:p> </o:p>
</td> </tr> <tr style=""> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt;" valign="top"> 3<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt;" valign="top"> BND337<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 79.85pt;" width="106" valign="top"> <o:p> </o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt;" width="78" valign="top"> <o:p> </o:p>
</td> </tr> <tr style=""> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt;" valign="top"> 4<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt;" valign="top"> OAB34_A<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 79.85pt;" width="106" valign="top"> <o:p> </o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt;" width="78" valign="top"> <o:p> </o:p>
</td> </tr> <tr style=""> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt;" valign="top"> 5<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt;" valign="top"> BND337_B<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 79.85pt;" width="106" valign="top"> <o:p> </o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt;" width="78" valign="top"> <o:p> </o:p>
</td> </tr> <tr style=""> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt;" valign="top"> 6<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt;" valign="top"> AOC115_A<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 79.85pt;" width="106" valign="top"> <o:p> </o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt;" width="78" valign="top"> <o:p> </o:p>
</td> </tr> <tr style=""> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt;" valign="top"> 7<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt;" valign="top"> AOC115_B<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 79.85pt;" width="106" valign="top"> <o:p> </o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt;" width="78" valign="top"> <o:p> </o:p>
</td> </tr> <tr style=""> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt;" valign="top"> 8<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt;" valign="top"> BND337_B<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 79.85pt;" width="106" valign="top"> <o:p> </o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt;" width="78" valign="top"> <o:p> </o:p>
</td> </tr> <tr style=""> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt;" valign="top"> 9<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt;" valign="top"> AOC115_C<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 79.85pt;" width="106" valign="top"> <o:p> </o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt;" width="78" valign="top"> <o:p> </o:p>
</td> </tr> <tr style=""> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt;" valign="top"> 10<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt;" valign="top"> CAT877<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 79.85pt;" width="106" valign="top"> <o:p> </o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt;" width="78" valign="top"> <o:p> </o:p>
</td> </tr> <tr style=""> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt;" valign="top"> 11<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt;" valign="top"> AOC115_D<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 79.85pt;" width="106" valign="top"> <o:p> </o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt;" width="78" valign="top"> <o:p> </o:p>
</td> </tr> <tr style=""> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt;" valign="top"> 12<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt;" valign="top"> JAT334<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 79.85pt;" width="106" valign="top"> <o:p> </o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt;" width="78" valign="top"> <o:p> </o:p>
</td> </tr> <tr style=""> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt;" valign="top"> 13<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt;" valign="top"> OAB34<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 79.85pt;" width="106" valign="top"> <o:p> </o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt;" width="78" valign="top"> <o:p> </o:p>
</td> </tr> <tr style=""> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt;" valign="top"> 14<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt;" valign="top"> COB376<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 79.85pt;" width="106" valign="top"> <o:p> </o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt;" width="78" valign="top"> <o:p> </o:p>
</td> </tr> <tr style=""> <td style="border-style: none solid solid; border-color: -moz-use-text-color black black; border-width: medium 1pt 1pt; padding: 0in 5.4pt;" valign="top"> 15<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt;" valign="top"> COB376_A<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 79.85pt;" width="106" valign="top"> <o:p> </o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color black black -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; width: 58.5pt;" width="78" valign="top"> <o:p> </o:p>
</td> </tr> </tbody></table> Table 1: MAIN CODES AND PRICES( from source file)
<table class="MsoNormalTable" style="border: medium none ; width: 135.55pt; border-collapse: collapse; margin-left: 6.75pt; margin-right: 6.75pt;" width="181" align="left" border="1" cellpadding="0" cellspacing="0"> <tbody><tr style="height: 18.5pt;"> <td style="border: 1pt solid windowtext; padding: 0in 5.4pt; background: rgb(141, 179, 226) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous; height: 18.5pt;" valign="top"> <o:p> </o:p>
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0in 5.4pt; background: rgb(141, 179, 226) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous; height: 18.5pt;" valign="top"> A<o:p></o:p>
</td> <td style="border-style: solid solid solid none; border-color: windowtext windowtext windowtext -moz-use-text-color; border-width: 1pt 1pt 1pt medium; padding: 0in 5.4pt; background: rgb(141, 179, 226) none repeat scroll 0% 0%; -moz-background-clip: border; -moz-background-origin: padding; -moz-background-inline-policy: continuous; height: 18.5pt;" valign="top"> B<o:p></o:p>
</td> </tr> <tr style="height: 20.4pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; height: 20.4pt;" valign="top"> 1<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; height: 20.4pt;" valign="top"> Product codes<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; height: 20.4pt;" valign="top"> prices<o:p></o:p>
</td> </tr> <tr style="height: 15.35pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; height: 15.35pt;" valign="top"> 2<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; height: 15.35pt;" valign="top"> AOC115<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; height: 15.35pt;" valign="top"> £15.99<o:p></o:p>
</td> </tr> <tr style="height: 16pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; height: 16pt;" valign="top"> 3<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; height: 16pt;" valign="top"> BND337<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; height: 16pt;" valign="top"> £10.99<o:p></o:p>
</td> </tr> <tr style="height: 21.65pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; height: 21.65pt;" valign="top"> 4<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; height: 21.65pt;" valign="top"> OAB34<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; height: 21.65pt;" valign="top"> £8.99<o:p></o:p>
</td> </tr> <tr style="height: 10.9pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; height: 10.9pt;" valign="top"> 5<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; height: 10.9pt;" valign="top"> CAT877<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; height: 10.9pt;" valign="top"> £12.99<o:p></o:p>
</td> </tr> <tr style="height: 16pt;"> <td style="border-style: none solid solid; border-color: -moz-use-text-color windowtext windowtext; border-width: medium 1pt 1pt; padding: 0in 5.4pt; height: 16pt;" valign="top"> 6<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; height: 16pt;" valign="top"> COB376<o:p></o:p>
</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color windowtext windowtext -moz-use-text-color; border-width: medium 1pt 1pt medium; padding: 0in 5.4pt; height: 16pt;" valign="top"> £3.99<o:p></o:p>
</td> </tr> </tbody></table> <o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>
<o:p> </o:p>






Hi guys, here is a problem.....



Assuming that we have different products codes(skus) in column “A” in table2. they refer to different items but some of them are suffixed with “_A”,”_B”,_”C” and “_D” . These codes refer to the same item. For example, AOC115, AOC115_A, AOC115_B, AOC115_C, AOC115_D refer to the same item and so on. The codes are not arranged in any order but will appear in random positions.
What I want to do is to apply prices to all these products from the source table1 to column “C” in table 2. So if for example, AOC115= £15.99, it should apply the same to the rest of its variants too. And similarly if “BND337” is = £10.99, the rest should get the same price. One way I think can be to trim the “-A” etc and move the codes to column B where same codes will get the same price ( but im not sure as this will affect the source codes too)…. Please help.
Many Thanks<o:p></o:p>
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

Blade Hunter

Well-known Member
Joined
Mar 13, 2008
Messages
3,147
Use a Vlookup and the lookup_value will be left(A2,6)

Put this in C2 and drag down.

=vlookup(Left(A2,6),'MAIN CODES AND PRICES'!A:B,2,0)
 

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
Welcome to the Board!

For the sake of not overlooking some rules, why not use a table of all possible codes/prices and VLOOKUP()?

HTH,
 

T. Valko

Well-known Member
Joined
May 9, 2009
Messages
16,623
Try this...

Table 1 in the range J2:K15

=VLOOKUP(LEFT(A2,FIND("_",A2&"_")-1),J$2:K$15,2,0)

Copy down as needed
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,916

ADVERTISEMENT

If the Main Codes and Prices are sorted by Product code,

=VLOOKUP(A2, 'MAIN CODES AND PRICES'!A:B,2,0) would work.
 

bosco_yip

Well-known Member
Joined
Dec 2, 2002
Messages
1,938
Office Version
  1. 2019
Platform
  1. Windows
Another option :

Table 2, C2 formula and fill down :

=LOOKUP(1,-FIND('MAIN CODES AND PRICES'!A$2:A$6,A2),'MAIN CODES AND PRICES'!B$2:B$6)

Regards
Bosco
 

star7576

New Member
Joined
Dec 8, 2009
Messages
6

ADVERTISEMENT

Hi everyone, I will try today all these options and will let you know if they work. Thanks all for your help:)
 

star7576

New Member
Joined
Dec 8, 2009
Messages
6
try this...

Table 1 in the range j2:k15

=vlookup(left(a2,find("_",a2&"_")-1),j$2:k$15,2,0)

copy down as needed



......:) this worked exactly correct... Thanks. I tried the others but couldn't get proper results..( may be i couldnt do properly) but thanks all
 

Watch MrExcel Video

Forum statistics

Threads
1,123,052
Messages
5,599,511
Members
414,315
Latest member
Yolanda5050

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top