PRICELIST keyword search, works one whole word

makinmomb

Active Member
Joined
Dec 23, 2013
Messages
401
Sheet1

TEXT CODEDESCRIPTIONCODEPRICE
431 STARSHOWER SET101.000.23x
431 CARINASHOWER SET101.000.24x
461 STARSHOWER SET101.000.21x
461 CARINASHOWER SET101.000.22x
LO-266/041/10SINK MIXER WALL TYPE166.000.47x
LO-214-15PILLAR TAP 1/2166.000.45x
LO-252-15PILLAR TYPE BATH MIXER166.000.71x
<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 WORDLO-
TEXT CODEDESCRIPTIONCODEPRICE
<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))),"")
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

alvin-chung

Active Member
Joined
Nov 24, 2013
Messages
361
Hi, try this in A4 and copy to rest of the cells.

Spreadsheet Formulas
CellFormula
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>
 
Last edited:

makinmomb

Active Member
Joined
Dec 23, 2013
Messages
401

ADVERTISEMENT

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

Spreadsheet Formulas
CellFormula
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

Active Member
Joined
Dec 23, 2013
Messages
401

ADVERTISEMENT

Okay got you
 

makinmomb

Active Member
Joined
Dec 23, 2013
Messages
401
ALVIN I have created a set of working files from your EXACT answers

Thank you once again
 

Forum statistics

Threads
1,136,354
Messages
5,675,296
Members
419,559
Latest member
BraytonM

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
Top