Search and IF functions

So I'm trying to put together a spreadsheet that will search for certain text in a range of cells and if it finds it copy other cells in the row onto the original worksheet.

For Example:

On Worksheet 1 I have the name John Smith

On Worksheet 2 I have a whole list of names and next to those names in the same row numbers in different columns

I want to set up a formula where it will search for 'John Smith' in that list of names and when if it finds that name copy the numbers next to it into the cells next to the name of worksheet 1.

Hopefully that makes sense, if not let me know and ill make a dummy workbook with an example.

Thanks!

Welcome to the forum, RpGroves. a sample workbook always helps. But if my eg works for you, then great. If you are looking for a simple exact match of John Smith, a simple VLOOKUP will do.
=VLOOKUP(A2,Sheet2!A:B,2,0)

if you are looking for a cell containing John Smith, then maybe:
=LOOKUP(99^99,SEARCH(A2,Sheet2!$D$1:$D$3),Sheet2!$E$1:$E$3)

=INDEX(Sheet2!$B$14:$L$21,MATCH(Sheet1!$A2,Sheet2!$A$14:$A$21,0),MATCH(Sheet1!B$1,Sheet2!$B$10:$L$10,0))

Change Audit 1 to Audit1