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.

 

[ALINCO]

Correction 3,9,3

 

[Fluff]

Firstly try this array formula
=INDEX($A$1:$B$1000,SMALL(IF(ISNUMBER(SEARCH($H$16,$A$1:$A$1000)),ROW($A$1:$A$1000)),ROW(A1)),2)
Secondly what version of Xl are you running?

 

[Aladin Akyurek]

Maybe easier on you...

Control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($B$1:$B$1006,SMALL(IF(ISNUMBER(SEARCH("SY",$A$1:$A$1006)),ROW($B$1:$B$1006)-ROW($B$1)+1),ROW($1:1))),"")

 

[ALINCO]

Wow thanks Fluff. Running 2010. I have tried it in column "B" only so far. It will obviously be ok in the other columns with tweaking. I believe there is a way of avoiding #NUM ! that is populating unused cells down the results table.

Also I would like to add a 4th column "E" to populate column "K" with hyperlinks to corresponding PDF data sheets for the components... Possible?

 

[ALINCO]

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

 

[Fluff]

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

 

[Fluff]

For the second part of your question in post#6have a look here
https://www.ablebits.com/office-addins-blog/2017/03/23/excel-hyperlink-function/

 

[ALINCO]

Ok alls well so far. Thanks to you both. will check your link

 

[Fluff]

You're welcome & thanks for the feedback