How to return multiple values (columns and rows) based on single criteria

Hello Excel Forum,
I have an Excel workbook with 2 worksheets:

"Kits" - is the master data; "Kit" part numbers in column A and the "Kit" description in column B; Column C contains the "Component" parts that make up the "Kit"; columns D:J contain data specific to each "Component Part". A Kit can have anywhere from 2 - 150+ Component Parts

"LookUp Tool" - is where I would like users to select a "Kit" number(s) from the DV dropdown in A2:A5 and have all of the "Component Part" number(s) and subsequent related data populate in cells A7:HXXX


The "Kits" worksheet is saved as a table; column A has Defined Name Kit_Part_Number

I have selected Kit Part # 1000522 in the LookUp Tool and copy/pasted the component parts simply to show the desired results

Don't worry about blanks as the finished product will have all data filled in.

Thank you in advance!!

Welcome to the forum! Please take a moment to re-read forum rule #1 and then amend your thread title to something that better explains your problem. Changing your thread title is not optional, which means you must change it. Thanks!

• Use concise, accurate thread titles.
• Your post title should describe your problem, not your anticipated solution.
• Use terms appropriate to a Google search - poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice tell us nothing.
• Responding to a request to change your thread title by doing so is mandatory.

To change a title go to your first post, click EDIT then Go Advanced and change your title.

No help to be offered, please, until the OP complies with this request.