not exact search help

[ALINCO]

Hi all, I don't do spread sheets often and so am not fluent at all. I have muddled my way to a point that has given me an "almost working to my needs" data base.
I have 4 sets of drawer units
Column "A" lists up to 1006 electronic components.
Column "B" lists 4 x 50 drawer units containing resistors, transistors, capacitors etc
Column "C" lists the individual drawers / 3 partitions
Column "D" lists the 3 partitions within each drawer... 3,2,1
Formula below is in "H,I,J" 3 down to 10 to output, from "H16" search cell, 3 results.

{=IF(ISERROR(INDEX($A$1:$B$1006,SMALL(IF($A$1:$A$1006=$H$16,ROW($A$1:$A$1006)),ROW(1:1)),2)),"",INDEX($A$1:$B$1006,SMALL(IF($A$1:$A$1006=$H$16,ROW($A$1:$A$1006)),ROW(1:1)),2))}

(Obviously this is slightly different for the other two columns.)

If I search for "SY320" the output cells return "3,8,2", drawer set 3, drawer 8, section 2.
If I search for "SY360" the output cells return "3,8,2", drawer set 3, drawer 9, section 3.

Could anyone give me the formula or alter the above that allows me to input search for example "SY" to return both the above results, and indeed any other combination of input string.

Thank you and apologies in advance for any error in etiquette regarding terminology or my posting method if incorrect.

 

[Aladin Akyurek]

Thank you, SY is an example only. It could be any combination of number and/or letters....

You can have it in a cell of its own and refer to that cell in the formula.

 

[Aladin Akyurek]

In that case you can use IfError as shown by Aladin rather than If(IsError
=IFERROR(INDEX($A$1:$B$1000,SMALL(IF(ISNUMBER(SEARCH($H$16,$A$1:$A$1000)),ROW($A$1:$A$1000)),ROW(A1)),2),"")

The ROW bits you have are not robust. Adding rows in front of the row of A1 will create havoc.

 

[Fluff]

The ROW bits you have are not robust. Adding rows in front of the row of A1 will create havoc.

OK, thanks for that.

 

[ALINCO]

Ok I can see it needs to be something like...

=HYPERLINK("#"&CELL("address",IFERROR(INDEX($A$1:$F$1000,SMALL(IF(ISNUMBER(SEARCH($H$3,$A$1:$A$1000)),ROW($A$1:$A$1000)),ROW(A1)),6),""))

... but no idea how to join the dots. The link will be in column "F" and the link to click to the PDF needs to land in column "N"

 

[Fluff]

If your link in col F is like
C:\MrExcel\book1.pdf

then use
=HYPERLINK(IFERROR(INDEX($F$1:$F$1000,SMALL(IF(ISNUMBER(SEARCH($H$3,$A$1:$A$1000)),ROW($A$1:$A$1000)-ROW($A$1)+1),ROWS($1:1))),""))

 

[ALINCO]

Looks good now, probably need some other tweaking later.

Great help Guys. Thank you

 

[Fluff]

You're welcome