EXCEL Find variable data from one sheet and copy entire row to another sheet

I apologize in advanced if this has been asked before. I cannot find an answer no matter how hard I try.

I have a spread sheet with multiple sheets that contain columns pertaining to staff names and payroll numbers.
I have tried VLOOKUP with no success as the data is based off may unsorted lists and have formulas in place which make it impossible to continually sort and resort the data.

I am searching for a Marco/VBA code that will allow me to input either a name or payroll number from one sheet called (Lookup) and find this data in the sheet called (From Adobe) from either column B (Payroll Number) or C (Name). Each column has formulas to accurately retrieve the data from another sheet which I do not want to break. When the data is found I would like the entire row corresponding to either the Payroll Number or Name to be copied back onto the sheet (Lookup) in another row (row 10) so that it does not corrupt the search data. It needs to overwrite any data that is already there. Effectively giving the appearance of a VLOOKUP process.

The names are stored in one column as First Name then Surname with one space separating them, not multiple columns. If I could search via surname or first name or even a partial search rather than an exact search that would be ideal.

Either having a pop up screen asking for the data I am looking for or entering the search criteria into a cell (B4) on sheet (Lookup) is acceptable.

Thanks ahead of time.

This seems easier if you have a dummy spreadsheet for us to work with.

Are you able to de-sensitise the data and upload a dummy spreadsheet here? I suspect this can be done with a formula.

How can I upload a file?

Right at the bottom of this page you'll see a button that says "Go Advanced". Click that.

Once the page loads, at the bottom there should be a section that says "Attachments", click on the link that says "Manage Attachments".

From there, at the very top you can click on "Browse" and locate the file. Then click "Upload". And once that is done, go right to the bottom and "Close Window".

The file should be attached then.

Thank you for the advice.
Please forgive if the file is clunky.

So, the reason why your VLOOKUP isn't working is because the input you have in 'Vlookup' cell B4 are numbers, whereas the payroll numbers in the 'From Adobe' sheet are texts.

Either, change the formula in
'From Adobe' cell B2:=

Formula:

Please Login or Register  to view this content.

OR, change the formula in...
'Vlookup' cell B8:=

Formula:

Please Login or Register  to view this content.

The same TEXT function will need to be applied to the other VLOOKUPs.

That should do the trick, no Macro required.

Duplicated message. Deleted.

I plan to ensure the source names are correct. I tried this on the one spreadsheet with some good results for partial names entered.

Please Login or Register  to view this content.

The next thing is to return a list of matching partial names, for example, looking up all names "Jane" rather than the first.
I'll post new threads for these questions.

I appreciate your help. I'll mark this thread as Solved.