Partial VLOOKUP/INDEX/MATCH in reverse

P

Puffin617

New Member
Joined
Nov 13, 2008
Messages
27
Hi all,

Here's my challenge : I have to look up a corresponding entry in a table but my source data that I have to use as search string contains MORE text on the end of the string that is in my look up table.

Here are some images to explain :

lPsOWJZ
http://db.tt/lPsOWJZ

fBoOJIT
http://db.tt/fBoOJIT

In the first pic BEPC10095W is located in F11. In my look up table (second Pic) "BEPC" is located in A19. By using BEPC10095W as my search string, I want to find "AUC QC 05" in B19 and put it next to BEPC10095W in my sheet.

How could I achieve this?
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
Sort by date Sort by votes
Puffin617 said:
Hi all,

Here's my challenge : I have to look up a corresponding entry in a table but my source data that I have to use as search string contains MORE text on the end of the string that is in my look up table.

Here are some images to explain :

lPsOWJZ
http://db.tt/lPsOWJZ

fBoOJIT
http://db.tt/fBoOJIT

In the first pic BEPC10095W is located in F11. In my look up table (second Pic) "BEPC" is located in A19. By using BEPC10095W as my search string, I want to find "AUC QC 05" in B19 and put it next to BEPC10095W in my sheet.

How could I achieve this?
Click to expand...

Try to post tiny samples directly here.
 
Upvote 0
Hi Puffin617......

Please copy paste a sample of your data to the forum directly. This forum does not show your pics.
 
Upvote 0
Main Sheet (where looked up data needs to be entered)

<table border="0" cellpadding="0" cellspacing="0" width="672"><tbody><tr style="height:15.0pt" height="20"><td style="height:15.0pt;width:53pt" height="20" width="70">ProjectNr</td> <td style="width:37pt" width="49">Batch</td> <td style="width:197pt" width="262">FieldReference</td> <td style="width:42pt" width="56">Ordno</td> <td style="width:68pt" width="90">
</td> <td style="width:109pt" width="145">SampleName</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">S2010_795</td> <td align="right">125346</td> <td>W/VAL SMELTER_2010343</td> <td align="right">2742264</td> <td>
</td> <td>BLPC10181W</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">S2010_795</td> <td align="right">125346</td> <td>W/VAL SMELTER_2010343</td> <td align="right">2742267</td> <td>
</td> <td>MCPC10204W</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">S2010_795</td> <td align="right">125374</td> <td>W/VAL SMELTER_2010347</td> <td align="right">2743586</td> <td>
</td> <td>RTWS2010347</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">S2010_795</td> <td align="right">125467</td> <td>W/VAL SMELTER_2010352</td> <td align="right">2746684</td> <td>
</td> <td>SSBWS2010352</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">S2010_795</td> <td align="right">125468</td> <td>W/VAL SMELTER_2010353</td> <td align="right">2746880</td> <td>
</td> <td>RTWS2010353</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">S2010_795</td> <td align="right">125496</td> <td>W/VAL SMELTER_2010356</td> <td align="right">2748339</td> <td>
</td> <td>FS1+210335W</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">S2010_795</td> <td align="right">125496</td> <td>W/VAL SMELTER_2010356</td> <td align="right">2748344</td> <td>
</td> <td>WACACP2010356</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">S2010_795</td> <td align="right">125506</td> <td>W/VAL SMELTER_2010357</td> <td align="right">2748766</td> <td>
</td> <td>BCCS10005W</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">S2010_795</td> <td align="right">125506</td> <td>W/VAL SMELTER_2010357</td> <td align="right">2748768</td> <td>
</td> <td>BEPC10095W</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">S2010_795</td> <td align="right">125506</td> <td>W/VAL SMELTER_2010357</td> <td align="right">2748769</td> <td>
</td> <td>BLPC10184W</td> </tr> </tbody></table>


Lookup table
<table border="0" cellpadding="0" cellspacing="0" width="449"><tbody><tr style="height:21.0pt" height="28"><td class="xl65" style="height:21.0pt;width:104pt" height="28" width="139">Stream Code</td> <td class="xl65" style="width:71pt" width="94">Grade</td> <td class="xl65" style="width:162pt" width="216">QC</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl67" style="height:15.75pt;width:104pt" height="21" width="139">
</td> <td class="xl67" style="width:71pt" width="94">
</td> <td class="xl67" style="width:162pt" width="216">
</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">250FS</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">MUC QC 07</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">250THI</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">MUC QC 07</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">AMCPS</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">AMC QC 05</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">AMUGW</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">AUC QC 05</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">AMUS</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">AMC QC 05</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">ANUCP</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">AUC QC 05</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">ANUCW</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">AUC QC 05</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">ANUGUS</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">AUC QC 05</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">AQPC1</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">AUC QC 05</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">AQPC2</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">AUC QC 05</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">AQPC3</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">AUC QC 05</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">AU1CI</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">AUC QC 05</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">AUG09W</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">AUC QC 05</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">AUGPS</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">AUC QC 05</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">AUGUS</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">AUC QC 05</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">BDCL</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">AMC QC 05</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">BEPC</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">AUC QC 05</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">BLPC</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">AMC QC 05</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">BRFDC</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">BMC QC 07</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">BRPWS</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">BMC QC 07</td> </tr> <tr style="height:15.75pt" height="21"> <td class="xl68" style="height:15.75pt;width:104pt" height="21" width="139">BSPC</td> <td class="xl68" style="width:71pt" width="94">Concentrate</td> <td class="xl68" style="width:162pt" width="216">AUC QC 05</td> </tr> </tbody></table>
 
Upvote 0
Try this

<TABLE style="WIDTH: 558pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=743 border=0><COLGROUP><COL style="WIDTH: 128pt; mso-width-source: userset; mso-width-alt: 6253" width=171><COL style="WIDTH: 59pt; mso-width-source: userset; mso-width-alt: 2852" width=78><COL style="WIDTH: 130pt; mso-width-source: userset; mso-width-alt: 6326" width=173><COL style="WIDTH: 41pt; mso-width-source: userset; mso-width-alt: 1974" width=54><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2596" width=71><COL style="WIDTH: 86pt; mso-width-source: userset; mso-width-alt: 4205" width=115><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 128pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=171 height=34>ProjectNr

</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>Batch</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=173>FieldReference</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=54>Ordno</TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71></TD><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=115>SampleName</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 61pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" width=81></TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 128pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=171 height=34>S2010_795</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>125346</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=173>W/VAL SMELTER_2010343</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=54>2742264</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71> </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=115>BLPC10181W</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">AMC QC 05</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 128pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=171 height=34>S2010_795</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>125346</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=173>W/VAL SMELTER_2010343</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=54>2742267</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71> </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=115>MCPC10204W</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 128pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=171 height=34>S2010_795</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>125374</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=173>W/VAL SMELTER_2010347</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=54>2743586</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71> </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=115>RTWS2010347</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 128pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=171 height=34>S2010_795</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>125467</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=173>W/VAL SMELTER_2010352</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=54>2746684</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71> </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=115>SSBWS2010352</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 128pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=171 height=34>S2010_795</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>125468</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=173>W/VAL SMELTER_2010353</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=54>2746880</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71> </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=115>RTWS2010353</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 128pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=171 height=34>S2010_795</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>125496</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=173>W/VAL SMELTER_2010356</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=54>2748339</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71> </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=115>FS1+210335W</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 128pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=171 height=34>S2010_795</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>125496</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=173>W/VAL SMELTER_2010356</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=54>2748344</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71> </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=115>WACACP2010356</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 128pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=171 height=34>S2010_795</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>125506</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=173>W/VAL SMELTER_2010357</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=54>2748766</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71> </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=115>BCCS10005W</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent" align=middle>#N/A</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 128pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=171 height=34>S2010_795</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>125506</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=173>W/VAL SMELTER_2010357</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=54>2748768</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71> </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=115>BEPC10095W</TD><TD class=xl69 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">AUC QC 05</TD></TR><TR style="HEIGHT: 25.5pt" height=34><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 128pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=171 height=34>S2010_795</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 59pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=78>125506</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 130pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=173>W/VAL SMELTER_2010357</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 41pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=54>2748769</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=71> </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; WIDTH: 86pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=115>BLPC10184W

</TD><TD class=xl69 id=td_post_2766396 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: transparent">AMC QC 05</TD></TR></TBODY></TABLE>

The formula in G Column is
<TABLE style="WIDTH: 61pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=81 border=0><COLGROUP><COL style="WIDTH: 61pt; mso-width-source: userset; mso-width-alt: 2962" width=81><TBODY><TR style="HEIGHT: 25.5pt" height=34><TD class=xl70 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 61pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=81 height=34> =INDEX($C$85:$C$105,MATCH(LEFT(F70,4),$A$85:$A$105,0),0)</TD></TR></TBODY></TABLE>
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Solved
Selective Match/Vlookup (?) on Various Criteria in Single Row
Replies
2
Views
275
m3ko
M
D
  • Question Question
Very complex - Vlookup or Index/Match etc...
Replies
7
Views
654
DarkSmile
D
C
  • Solved
Index Match across worksheets with criteria
Replies
5
Views
577
Fluff
Fluff
A
  • Question Question
Index Match Array formula partially working Google sheets
Replies
6
Views
2K
Fluff
Fluff
S
  • Solved
VLOOKUP partial text match
Replies
7
Views
2K
AlanY
A

Forum statistics

Threads
1,224,600
Messages
6,179,833
Members
452,947
Latest member
Gerry_F

Share this page

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
Back
Top