How to improve my INDEX & MATCH combo on text searches?

[The Zealot]

I'm having an issue with matching the text I have in a cell with the Model of the equipment to the list of Products, which have the Product ID (what I want to get) and the Product's description, which usually has the Model name along with some description separated by a hifen.
(Don't ask why would someone make such a basic data structure mistake for not keeping the Model name in its own separate field, it makes me boil! )

So anyway, lets say I have a kit's model name of SFR18 and I'm trying to get its product code. Unfortunately it doesn't work as expected, because it yielded the code "PD002383", used for "SFR08 - 2U Synapse Frame" instead of code "PD001350", used for "SFR18 - Synapse 4U Frame". BUT when using the same formula for the newer model, SFR18SR, then I get the correct code "PD001350", used for "SFR18 - Synapse 4U Frame"!
So how come Excel is too stupid to match an SFR18 to an SFR08 but smart enough to match an SFR18SR to an SFR18?

Then I investigated the results on other model names and found another inconsistency. The model "ARC20" was matched to code "PD003269", used for "ARC125 - Aspect Ratio Converter" instead of code "PD000100", used for "ARC-20 Aspect Ratio Converter".

That's when I noticed, "how come Excel is stopping just one row above of the correct product code?".
The Products list is over 2300 records long, and the first list of kit is 10000 records long, with two more lists around the same size in waiting. If I don't nail the formula right, I risk adding thousands of bogus products to the Inventory list of the company.

Here's the formula I have so far:

«INDEX(Products_Assigned_to_Assets!B6:C2311,MATCH(Prep!K25,Products_Assigned_to_Assets!B6:B2311),2)


Data sample that I have to match:

<table border="0" cellpadding="0" cellspacing="0" width="392"><colgroup><col style="mso-width-source:userset;mso-width-alt:7168; width:147pt" span="2" width="196"> </colgroup><tbody><tr style="mso-height-source:userset;height:18.75pt" height="25"> <td class="xl67" style="height:18.75pt;width:147pt" height="25" width="196">Model
SFR18</td> <td class="xl67" style="width:147pt" width="196">Formula
PD002383</td> </tr> <tr style="mso-height-source:userset;height:18.75pt" height="25"> <td class="xl67" style="height:18.75pt" height="25">HDS10 HD to SD Down Converter</td> <td class="xl67">PD000118</td> </tr> <tr style="mso-height-source:userset;height:18.75pt" height="25"> <td class="xl67" style="height:18.75pt" height="25">ARC20</td> <td class="xl67">PD003269</td> </tr> <tr style="mso-height-source:userset;height:18.75pt" height="25"> <td class="xl67" style="height:18.75pt" height="25">SFR18SR</td> <td class="xl67">PD001350</td> </tr> <tr style="mso-height-source:userset;height:18.75pt" height="25"> <td class="xl67" style="height:18.75pt" height="25">ARC20</td> <td class="xl67">PD003269</td> </tr> <tr style="mso-height-source:userset;height:18.75pt" height="25"> <td class="xl67" style="height:18.75pt" height="25">ARC20</td> <td class="xl67">PD003269</td> </tr> <tr style="mso-height-source:userset;height:18.75pt" height="25"> <td class="xl67" style="height:18.75pt" height="25">ARC20</td> <td class="xl67">PD003269</td> </tr> <tr style="mso-height-source:userset;height:18.75pt" height="25"> <td class="xl67" style="height:18.75pt" height="25">SDX08</td> <td class="xl67">#N/A</td> </tr> </tbody></table>
(The SDX08 is yielding as not found, even though it exists as "SDX08 - 8 SD-SDI to 1 SD-SDI switcher")



Data sample list of existing products:

<table border="0" cellpadding="0" cellspacing="0" width="529"><colgroup><col width="463"><col width="66"></colgroup><tbody><tr height="20"> <td class="xl67" style="height:15.0pt;width:347pt" height="20" width="463">Description</td> <td class="xl67" style="width:50pt" width="66">Code</td> </tr></tbody></table> <table border="0" cellpadding="0" cellspacing="0" width="529"><colgroup><col style="mso-width-source:userset;mso-width-alt:16932;width:347pt" width="463"> <col style="mso-width-source:userset;mso-width-alt:2413;width:50pt" width="66"> </colgroup><tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:347pt" height="20" width="463">SFR08 - 2U Synapse Frame</td> <td style="width:50pt" width="66">PD002383</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">SFR18 - Synapse 4U Frame</td> <td>PD001350</td> </tr> </tbody></table> <table border="0" cellpadding="0" cellspacing="0" width="529"><col style="mso-width-source:userset;mso-width-alt:16932;width:347pt" width="463"> <col style="mso-width-source:userset;mso-width-alt:2413;width:50pt" width="66"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:347pt" height="20" width="463">HDS10 - HD to SD Down Converter</td> <td style="width:50pt" width="66">PD000118</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">HDS100 - HD, SD down converter/synchronizer (3Gb/s upgradeable)</td> <td>PD002776</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">HDS110 - HD, SD Down Converter/Synchronizer With Audio Shuffler</td> <td>PD002561</td> </tr> </tbody></table> <table border="0" cellpadding="0" cellspacing="0" width="529"><col style="mso-width-source:userset;mso-width-alt:16932;width:347pt" width="463"> <col style="mso-width-source:userset;mso-width-alt:2413;width:50pt" width="66"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:347pt" height="20" width="463">ARC125 - Aspect Ratio Converter</td> <td style="width:50pt" width="66">PD003269</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ARC-20 Aspect Ratio Converter</td> <td>PD000100</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ARC20:20 Aspect Ratio Converter</td> <td>PD000862</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">ARC-2000 Aspect Ratio Converter</td> <td>PD000102</td> </tr> </tbody></table> <table border="0" cellpadding="0" cellspacing="0" width="529"><col style="mso-width-source:userset;mso-width-alt:16932;width:347pt" width="463"> <col style="mso-width-source:userset;mso-width-alt:2413;width:50pt" width="66"> <tbody><tr style="height:15.0pt" height="20"> <td style="height:15.0pt;width:347pt" height="20" width="463">SDR08 - SD-SD Reclocking DA (ASI/DVB Compatible)</td> <td style="width:50pt" width="66">PD000133</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">SDR08 - SD-SD Reclocking DA (NOT ASI/DVB Compatible)</td> <td>PD002543</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">SDR09 - Dual channel non-reclocking (N), reclocking (R) SDI DA</td> <td>PD001461</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">SDRK/12 - Rackmount KVM Extender Hub</td> <td>PD003272</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">SDU4 Surround Decoder Unit</td> <td>PD000268</td> </tr> <tr style="height:15.0pt" height="20"> <td style="height:15.0pt" height="20">SDX08 - 8 SD-SDI to 1 SD-SDI switcher</td> <td>PD002347</td> </tr> </tbody></table>


Thank you for your help!

 

[Aladin Akyurek]

Try...

=INDEX(Products_Assigned_to_Assets!$C$6:$C$2311,MATCH(Prep!K25,Products_Assigned_to_Assets!$B$6:$B$2311,0))

 

[The Zealot]

Sorry Aladin, not there yet. With 0 I only get N/As accross all records.
While I did get a yield this time for SDX08, the code I got was - again - the product above SDX08. So your formula yielded the code "PD000268", which as you can see by the list provided, belongs to product "SDU4 Surround Decoder Unit".
(for this I had to change the match number from 0 to 2 in your formula)

 

[number kruncher]

You could use a VLOOKUP to find your result, e.g.

=VLOOKUP(Prep!K25,Products_Assigned_to_Assets!B6:C2311,2,FALSE)

Could use Prep!K25&"*" above if K25 just used first SDX08 as key etc

HTH

 

[The Zealot]

Sorry number kruncher, but you missed the point. A Vlookup set to FALSE would yield very little to no matches. And if set to TRUE, the codes I get are completely wild and off the mark, unfortunately.

 

[The Zealot]

Could use Prep!K25&"*" above if K25 just used first SDX08 as key etc

HTH

Wait a minute, that's actually a bloomin' brilliant idea! The beginning should always be right, so adding an asterisk could do the trick!

Except on the ARC20 and the ARC2000, that could be an issue, but that massively shrinks the potential manual work! I'll give it a try!

 

[number kruncher]

Thanks for the feedback, it's really appreciated.

 

[Aladin Akyurek]

Sorry Aladin, not there yet. With 0 I only get N/As accross all records.
While I did get a yield this time for SDX08, the code I got was - again - the product above SDX08. So your formula yielded the code "PD000268", which as you can see by the list provided, belongs to product "SDU4 Surround Decoder Unit".
(for this I had to change the match number from 0 to 2 in your formula)

Match-type set to 0 is needed...

You might try the * wildcard:

=INDEX(Products_Assigned_to_Assets!$C$6:$C$2311,MATCH(Prep!K25&"*",Products_Assigned_to_Assets!$B$6:$B$2311,0))

But a closer look shows that

HDS10 HD to SD Down

will fail to match. Fuzzy matching might process such cases better:

http://www.mrexcel.com/forum/showthread.php?t=195635