Finding strings using only first characters

[lvisesteban]

Hi everyone,

Im facing some issues with the find/search function.

I have a example matrix

[TABLE="width: 87"]
<!--StartFragment--> <colgroup><col width="87" style="width:65pt"> </colgroup><tbody>[TR]
[TD="width: 87"]21650094-6[/TD]
[/TR]
[TR]
[TD]21650065-7[/TD]
[/TR]
[TR]
[TD]23730130-7[/TD]
[/TR]
[TR]
[TD]21436620-7[/TD]
[/TR]
[TR]
[TD]21724110-3[/TD]
[/TR]
[TR]
[TD]22852562-6[/TD]
[/TR]
[TR]
[TD]5792980-4[/TD]
[/TR]
[TR]
[TD]11215394-2[/TD]
[/TR]
[TR]
[TD]14993992-k[/TD]
[/TR]
[TR]
[TD]10812158-0[/TD]
[/TR]
<!--EndFragment--></tbody>[/TABLE]


and a search field where users read with a barcode pistol, but barcode are like "21650094ANF-8", this code it's similar to the first one on the list, but only the first part "21650094" and then the barcode have random characters. But I still need to find the first result. Or maybe if I only insert "216500" it could return to posibilites from the matrix (the first and the second one)

I tried using =SEARCH(LEFT(A1,6),MATRIX)

but got formula error, any suggestions?

thanks in advance

 

[Special-K99]

Assuming data list is in column A and search value in B1

in C1
=IFERROR(INDEX($A$1:$A$100,SMALL(IF(ISNUMBER(SEARCH(B$1,A$1:A$100)),ROW($A$1:$A$100)),ROW(A1)),1),"")
Array formula, use Ctrl-Shift-Enter

and copy down column C