How to search one item in Excel and locate many results?

I am trying to find out if there is a way to automate the ability for search criteria in excel to search a table of data, compare matches on specific lines to another list in the same sheet, and provide one or more results.

An example would be, a sales person enters a part number in (B2), the formula is set to search a table of data (C12 to P40) and then compare by the cell location where the matches were found, cross reference them with part numbers listed in cells B12 to B40, and then list comparable part numbers from B12-B40 in cells E2, E3, E4, etc.?

See attached for the workbook. I've seen it done in excel before but my example is a locked workbook...that of course, no one knows the password to.

Any guidance would be greatly appreciated!!!

I added similar comments within the workbook. See 'comments' in cells B2 and E2.

Thanks!

Test1.xlsx

Put this formula in R12:

=IF(ISNUMBER(MATCH($B$2,C12:P12,0)),MAX(R$11:R11)+1,"-")

and copy it down to R40.

Then put this formula in E2:

=IFERROR(INDEX(B:B,MATCH(ROWS($1:1),R:R,0)),"")

and copy that down to E7.

Hope this helps.

Pete

WOW! That was fast. Thanks so much for the help guys, I'll definitely make a note of this for the future.

Cheers!

If that takes care of your original question, please select Thread Tools from the menu above your first post and mark this thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete