List generation from lookup of values from columns then rows

Hi all,

Pulling my hair out with this one, Have searched google and forums but while I can find similar problems, I can't find the solution to my particular issue.

I have been tasked with finding a way of getting a list of part numbers based on user input of a pallette number. The pallette numbers are in columns from columns E to V. The part numbers in Column A and some other items in columns B-D.

What I need is for users to input a pallet number into a cell (on the 'input sheet'), and for excel to then list out all part numbers on that pallette (these are denoted by having a value in the column of the pallette number in the row of the part number); underneath where they put in the pallette number.

Please see attached file for details (which I have trimmed down - original file is much much larger). I have tried a whole host of index, small, vlookups, hlookups and countifs. but just cant get the combination right to get it working....if of course it is possible. Is there a solution to this?

Hope this makes sense. Appreciate any help given.

Thanks, Simon