Sheet1
<colgroup><col width="92" style="width: 69pt; mso-width-source: userset; mso-width-alt: 3364;">
<col width="150" style="width: 113pt; mso-width-source: userset; mso-width-alt: 5485;">
<col width="61" style="width: 46pt; mso-width-source: userset; mso-width-alt: 2230;">
<col width="46" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;">
<tbody>
</tbody>
Sheet2
<colgroup><col width="79" style="width: 59pt; mso-width-source: userset; mso-width-alt: 2889;">
<col width="94" style="width: 71pt; mso-width-source: userset; mso-width-alt: 3437;">
<col width="80" style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;">
<col width="46" style="width: 35pt; mso-width-source: userset; mso-width-alt: 1682;">
<tbody>
</tbody>
This formula a4 a5 a6 and a7 works fine
=IFERROR(INDEX(Sheet1!A$2:A$5,
SMALL(IF(ISNUMBER(SEARCH(" "&$C$1&" "," "&Sheet1!$A$2:$A$5&" ")),
ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1),ROWS(A$4:A4))),"")
The only problem is it does recognise whole words 431 , star , 431 star but does not recognise incomplete words LO-
What is the necessary change required ?
=IFERROR(INDEX(Sheet1!B$2:B$5,
SMALL(IF(ISNUMBER(SEARCH(" "&$C$1&" "," "&Sheet1!$A$2:$A$5&" ")),
ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1),ROWS(B$4:B4))),"")
TEXT CODE | DESCRIPTION | CODE | PRICE |
431 STAR | SHOWER SET | 101.000.23 | x |
431 CARINA | SHOWER SET | 101.000.24 | x |
461 STAR | SHOWER SET | 101.000.21 | x |
461 CARINA | SHOWER SET | 101.000.22 | x |
LO-266/041/10 | SINK MIXER WALL TYPE | 166.000.47 | x |
LO-214-15 | PILLAR TAP 1/2 | 166.000.45 | x |
LO-252-15 | PILLAR TYPE BATH MIXER | 166.000.71 | x |
Sheet2
ENTER KEY WORD | LO- | ||
TEXT CODE | DESCRIPTION | CODE | PRICE |
This formula a4 a5 a6 and a7 works fine
=IFERROR(INDEX(Sheet1!A$2:A$5,
SMALL(IF(ISNUMBER(SEARCH(" "&$C$1&" "," "&Sheet1!$A$2:$A$5&" ")),
ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1),ROWS(A$4:A4))),"")
The only problem is it does recognise whole words 431 , star , 431 star but does not recognise incomplete words LO-
What is the necessary change required ?
=IFERROR(INDEX(Sheet1!B$2:B$5,
SMALL(IF(ISNUMBER(SEARCH(" "&$C$1&" "," "&Sheet1!$A$2:$A$5&" ")),
ROW(Sheet1!$A$2:$A$5)-ROW(Sheet1!$A$2)+1),ROWS(B$4:B4))),"")