Search with multiply results

Hi,

I'm trying to work out the best way to search and display all partial matches related to one cell. What would be the best way to achieve this. I tried xlookup and could only get one result and lots of errors. I did look at index match and had no luck either.

In the attached file "Find me" B9 is the partial number. Looking in "Data" Column "B" and then transferring results from "Data" A:B into "Find me" D:E columns.

Any help would be much appreciated.

I've used a helper column in the Data sheet to identify matching records and to give each a unique serial number. Put this formula in H2 of that sheet:

=IF(LEFT(B2,LEN('Find Me'!B$9))='Find Me'!B$9,MAX(H$1:H1)+1,"-")

then copy down to the bottom of your data (i.e. to H3181). The hyphens help to show where the formula is active.

You can put this formula in B10 of the Find Me sheet:

=MAX(Data!H:H)&" matching records found"

to tell you how many matching records there are, and then use these formulae in the cells stated on the Find Me sheet:

D10: =IF(ROWS($1:1)>MAX(Data!$H:$H),"",INDEX(Data!$B:$B,MATCH(ROWS($1:1),Data!$H:$H,0)))

E10: =IF(D10="","",INDEX(Data!$A:$A,MATCH(ROWS($1:1),Data!$H:$H,0)))

You need to copy these two formulae down for as many rows as stated in cell B10 (it doesn't matter if you copy too far, as you will just get blanks).

Hope this helps.

Pete

Thanks Pete that worked well.

How would i go about making the "Find Me" D10:D3181 to be in ascending order?

Cheers