Pulling multiple index results from a single input number

[cwatsonSONA]

Please pardon me if there are any blatant errors in the following. I'm modifying an existing file, not my own, so I may be slightly out of my depth.

First, an example file:

Example for Forum.xlsx

I can't post the original, it's a company file. This is similar, however.

What should happen is the user selects an item number on the interface sheet, the data sheet pulls up all relevant component numbers and accompanying information for that item number, and it is then displayed on the interface sheet. The original file worked perfectly.

I copied the whole thing over, entered a new set of data in the table, defined a new name for the array, and changed what I thought were all the relevant parameters in the formulae. What's happening, though, is strange to me.

The original file had 220 rows of data in the table area. My new file has 600 rows. Even though I changed the array in the formulae it only correctly returns data from the first 220 rows. Enter any item number past that and it for some reason defaults to the first row of data in the table, regardless of the item, and leaves the reast blank.

As a side note, in case it's important, not all items have three components. I think that's why the original creator used the SMALL/ROW functions to make sure only the components for that item are displayed, regardless of how many there are.

Anyone have any ideas? I've wracked my brain for hours already.

[NBVC]

In M3, try:

=IFERROR(INDEX(Sandwiches,SMALL(IF(INDEX(Sandwiches,0,1)&""=$L$2&"",ROW(INDEX(Sandwiches,0,1))-MIN(ROW(INDEX(Sandwiches,0,1)))+1),ROW(1:1)),3),"")

confirmed with CTRL+SHIFT+ENTER and copied down.

Similar for other columns, changing the Column number (3) appropriately.

[cwatsonSONA]

THANK YOU! Problem solved beautifully!