PRICELIST keyword search, works one whole word

[makinmomb]

Sheet1

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

<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

ENTER KEY WORD	LO-
TEXT CODE	DESCRIPTION	CODE	PRICE

<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))),"")

 

[makinmomb]

Kindly quote formulas at 1000 rows sheet1 as expected past long data

 

[makinmomb]

Anyone ?

 

[alvin-chung]

Hi, try this in A4 and copy to rest of the cells.

Spreadsheet Formulas

Cell Formula A4 {=IFERROR(INDEX(Sheet1!A$2:A$1000, SMALL(IF(ISNUMBER(SEARCH($C$1,Sheet1!$A$2:$A$1000)), ROW(Sheet1!$A$2:$A$1000)-ROW(Sheet1!$A$2)+1),ROWS(A$4:A4))),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

 

[makinmomb]

Hi, try this in A4 and copy to rest of the cells.

Spreadsheet Formulas

Cell Formula A4 {=IFERROR(INDEX(Sheet1!A$2:A$1000, SMALL(IF(ISNUMBER(SEARCH($C$1,Sheet1!$A$2:$A$1000)), ROW(Sheet1!$A$2:$A$1000)-ROW(Sheet1!$A$2)+1),ROWS(A$4:A4))),"")} <tbody> </tbody>

Formula Array: Produce enclosing { } by entering formula with CTRL+SHIFT+ENTER!

<tbody>
</tbody>

Thank you it works

 

[makinmomb]

Not sure what you EXACTLY changed in the formula ?

 

[makinmomb]

Okay got you

 

[alvin-chung]

You're welcome
ps: I just removed the spaces and modified the range to 1000 rows

 

[makinmomb]

ALVIN I have created a set of working files from your EXACT answers

Thank you once again