Using VBA or Formula to search multiple rows of data and return specified column values (must return all results and not first match)

[SeanKC1989]

Afternoon All,

I am having some trouble trying to find a formula or script that is able to search for a specified value in around 40 columns (some will be blank) and then if a match is found then to return the data from either one specific column or a group of columns.
This step must then be repeated and all results for the match given (not just the first or last).
There are 500 or so rows so the script or formula must be able to handle multiple rows and columns simultaneously.

For example I have a value on sheet 3, I would like to search all rows in sheet 1 for this value and each time there is a match return the values from column 46 in sheet 1. Is this possible?

Please help

 

[SeanKC1989]

I did that, but it returns no data at all then :/ just runs but data goes nowhere

 

[mumps]

Can you post a screen shot of what your data looks like? Section B at this link has instructions on how to post a screen shot: https://www.mrexcel.com/forum/board-announcements/127080-guidelines-forum-use.html Alternately, you could upload a copy of your file to a free site such as www.box.com. or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. Include a detailed explanation of what you would like to do referring to specific cells, rows, columns and worksheets. If the workbook contains confidential information, you could replace it with generic data.