Ability to return a sub-list from a master list?

[ECJ53]

I am working with a master list that has about 400 rows of data.  The first column is an 8 digit number.

The first 5 digits of the number define a product line.  The last 3 digits identify a unique product.

I am trying to create a formula, that would allow someone to enter a 5 digit product line code, and have it copy on a blank worksheet all of the unique products that are identified with that product line?

There are about 6 columns that have information regarding each unique product and the idea is to copy those columns to the new worksheet.

Is this possible?  Can you point me in the right direction on what function to use?

Thanks

EJ

[#2]

@ECJ53 If you are an MS365 subscriber, your version of Excel might already recognise the FILTER function. If so, the attached workbook contains a working example of what I believe you are trying to achieve.

Using a structured table for the Master list, to make the filtered range extend automatically when you include new items in the master list.

[#3]

Yes is possible,, if you share some sample data or the workbook with us then, I'll show you that how could you achieve this,,!

[#4]

@ECJ53 

You need an Array (CSE) formula, fixes the issue, works on PARTIAL MATCH mechanism.

• This formula works with all versions of Excel.

{=IFERROR(INDEX(Sheet1!$A$2:$C$13, SMALL(IF(ISNUMBER(FIND($A$2, Sheet1!$A$2:$A$13)), MATCH(ROW(Sheet1!$A$2:$C$13), ROW(Sheet1!$A$2:$C$13)), ""), ROWS($A$1:A1)), COLUMNS($A$1:A1)), "")}

• Enter this formula in sheet2 cell C2, finish with Ctrl+Shift+Enter and fill across.
• As soon you update cell A2 with new value, formula works accordingly.
• Check the attached sheet.