Search list of "text strings" for *text*, results in drop down box

[jezscott]

This question is based on the same list of information as my previous post ...which had no replies, but I eventually figured it out

I'd like to be able to create a drop down box (validation list) that shows search results based on text entered in another cell.

So if I enter into a cell: *glass*

Then the drop down box would list the following:
L22106 - Greenhouses, glasshouses
L3343 - Glassfibre tendons
L6122 - Glass aggregate, expanded
L6332 - Glass
L6613 - Glass sheets
L6613131 - Stained glass
L6613132 - Engraved glass
etc.

My current validation list source formula is:
=OFFSET(TblLd,MATCH(Elem0S,TblLd,0)-1,2,COUNTIF(TblLd,Elem0S),1)
TblLd = Description Column of Table L
Elem0S = Cell where text is entered for search

This is supposed to search Column TblLd for the text in Elem0S and offset the displayed result from the column Combined.

I'd like the formula to display the same results as the criteria button in the Form box. However, strangely my formula returns the same number of results as the Form box, but only some of them are correct.

Here is an extract of one of the tables:

<table style="border-collapse: collapse; width: 709pt;" border="0" cellpadding="0" cellspacing="0" width="944"><col style="width: 56pt;" width="74"> <col style="width: 56pt;" width="75"> <col style="width: 173pt;" width="230"> <col style="width: 47pt;" width="62"> <col style="width: 377pt;" width="503"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl67" style="border-style: solid; border-color: rgb(79, 129, 189); border-width: 0.5pt 0.5pt 1pt; height: 15pt; width: 56pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri;" height="20" width="74">Order</td> <td class="xl69" style="border-style: solid solid solid none; border-color: rgb(79, 129, 189) rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: 0.5pt 0.5pt 1pt medium; width: 56pt; font-size: 11pt; color: blue; font-weight: 700; text-decoration: none; font-family: Calibri;" width="75">Element</td> <td class="xl70" style="border-style: solid solid solid none; border-color: rgb(79, 129, 189) rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: 0.5pt 0.5pt 1pt medium; width: 173pt; font-size: 11pt; color: blue; font-weight: 700; text-decoration: none; font-family: Calibri;" width="230">Description</td> <td class="xl68" style="border-style: solid solid solid none; border-color: rgb(79, 129, 189) rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: 0.5pt 0.5pt 1pt medium; width: 47pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri;" width="62">Group</td> <td class="xl65" style="border-style: solid solid solid none; border-color: rgb(79, 129, 189) rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: 0.5pt 0.5pt 1pt medium; width: 377pt; font-size: 11pt; color: black; font-weight: 700; text-decoration: none; font-family: Calibri;" width="503">Combined</td> </tr> <tr style="height: 15pt;" height="20"> <td style="border-style: none solid solid; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189); border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="20">0661313</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L6613131</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Stained glass</td> <td class="xl66" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L661313</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L6613131 - Stained glass</td> </tr> <tr style="height: 15pt;" height="20"> <td style="border-style: none solid solid; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189); border-width: medium 0.5pt 0.5pt; height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;" height="20">0661313</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri;">L6613132</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri;">Engraved glass</td> <td class="xl66" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;">L661313</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;">L6613132 - Engraved glass</td> </tr> <tr style="height: 15pt;" height="20"> <td style="border-style: none solid solid; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189); border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="20">0661313</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L6613133</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Other decorative finishes for glass</td> <td class="xl66" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L661313</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L6613133 - Other decorative finishes for glass</td> </tr> <tr style="height: 15pt;" height="20"> <td style="border-style: none solid solid; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189); border-width: medium 0.5pt 0.5pt; height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;" height="20">0661313</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri;">L6613134</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri;">Window leading materials</td> <td class="xl66" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;">L661313</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;">L6613134 - Window leading materials</td> </tr> <tr style="height: 15pt;" height="20"> <td style="border-style: none solid solid; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189); border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="20">1212311</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L212311</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Complete culvert systems</td> <td class="xl66" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L21231</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L212311 - Complete culvert systems</td> </tr> <tr style="height: 15pt;" height="20"> <td style="border-style: none solid solid; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189); border-width: medium 0.5pt 0.5pt; height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;" height="20">1212312</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri;">L212312</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri;">Culvert components</td> <td class="xl66" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;">L21231</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;">L212312 - Culvert components</td> </tr> <tr style="height: 15pt;" height="20"> <td style="border-style: none solid solid; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189); border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="20">1212331</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L212331</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Filter drains and channels</td> <td class="xl66" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L21233</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L212331 - Filter drains and channels</td> </tr> <tr style="height: 15pt;" height="20"> <td style="border-style: none solid solid; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189); border-width: medium 0.5pt 0.5pt; height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;" height="20">1216111</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri;">L216111</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri;">Complete canal locks</td> <td class="xl66" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;">L21611</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;">L216111 - Complete canal locks</td> </tr> <tr style="height: 15pt;" height="20"> <td style="border-style: none solid solid; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189); border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="20">1216112</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L216112</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Canal lock components</td> <td class="xl66" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L21611</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L216112 - Canal lock components</td> </tr> <tr style="height: 15pt;" height="20"> <td style="border-style: none solid solid; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189); border-width: medium 0.5pt 0.5pt; height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;" height="20">1216131</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri;">L216131</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri;">Complete dams</td> <td class="xl66" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;">L21613</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;">L216131 - Complete dams</td> </tr> <tr style="height: 15pt;" height="20"> <td style="border-style: none solid solid; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189); border-width: medium 0.5pt 0.5pt; background: rgb(219, 229, 241) none repeat scroll 0% 0%; height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;" height="20">1216132</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L216132</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">Dam components</td> <td class="xl66" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L21613</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; background: rgb(219, 229, 241) none repeat scroll 0% 0%; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri; -moz-background-clip: -moz-initial; -moz-background-origin: -moz-initial; -moz-background-inline-policy: -moz-initial;">L216132 - Dam components</td> </tr> <tr style="height: 15pt;" height="20"> <td style="border-style: none solid solid; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189); border-width: medium 0.5pt 0.5pt; height: 15pt; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;" height="20">1216141</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri;">L216141</td> <td class="xl71" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: blue; font-weight: 400; text-decoration: none; font-family: Calibri;">Complete spillways</td> <td class="xl66" style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;">L21614</td> <td style="border-style: none solid solid none; border-color: -moz-use-text-color rgb(79, 129, 189) rgb(79, 129, 189) -moz-use-text-color; border-width: medium 0.5pt 0.5pt medium; font-size: 11pt; color: black; font-weight: 400; text-decoration: none; font-family: Calibri;">L216141 - Complete spillways</td> </tr> </tbody></table>
Column description:
Order = added to get another validation drop down list to work (orders by length of code first, then number)
Element = the code
Description = code's description
Group = parent code the current code belongs to
Combined = used with offset() to display correctly in drop down lists


Should I alter the tables or alter the formula or something else entirely?

 

[Andrew Poulsom]

Your formula is fine for the sample data you posted. For it to work all the items containing the search text must be in a contiguous range.

 

[jezscott]

Thanks,
what do you mean by a contiguous range?

 

[jezscott]

Here is a cut down version of my spreadsheet:
http://www.mediafire.com/?igoiykupkj1

 

[Andrew Poulsom]

Thanks,
what do you mean by a contiguous range?

They have to be all together, one cell below the other. You formula finds the first item then uses COUNTIF for the number of rows to return.

 

[jezscott]

ah, yes the list is one cell below the other.

With my current formula it seems to matter how the list is ordered.

It appears that it is correctly counting the number of rows that contain the *text* but it is listing that number of rows below the first found item.

Eg. If I type *glass* I get 22 results starting from the first row that includes glass, followed by the next 21 rows that follow this row (which do not include the word glass)

It feels like it is nearly there, but I'm missing something.

 

[Andrew Poulsom]

If you have 22 items containing glass they have to be in a block of cells one below the other. They can't be scattered about, because your formula finds the first item and returns that cell and the 21 below it.

You can't use OFFSET to return all the cells containing glass if they aren't contiguous because OFFSET can only return a contiguous range.

 

[Kevlarhead]

If you can't ensure all your cells with 'glass' are in a continuous block, the OFFSET approach will not work.

Suggest SQL may be your next stop.

 

[jezscott]

Thats a shame, I have been using this formula (found elsewhere on the internet) and just reordering the table rows to suit it.

I'll look into SQL.

 

[jezscott]

SQL seems to be for searching external databases, it looks a little daunting (I haven't even tried VBA yet)

I wonder if I could make a very long formula that finds the first item, then looks again skipping the previous result and so on up to a limited amount of results that would hopefully be enough.

Or make another intermediate worksheet based on the main table that provides a contiguous list of results.