I have one sheet (sheet1) that has all of the data I need. Another sheet (sheet2) where I'm looking to enter in a name then have formulas set up to grab some of the data within the row that contains that name in sheet1.
So essentially my aim is to do something like this, the bold bits are what I need to grab from sheet 1 based on the name in sheet 2
Sheet1
Email | Name | Data1 | Data2 | Data3
Sheet2
Date | Name | Email | Notes | Data1 | Data2 | Data3
If you can reformat your Sheet2 and place the columns in bold beside each other, you can save yourself with only using a single formula:
=ArrayFormula(if(len($B$2:$B),iferror(vlookup($B$2:$B,{Sheet1!$B$2:$B,Sheet1!$A$2:$A,Sheet1!$C$2:$E},{2,3,4,5},)),))
If not, then you'd have to separate the formula into 2, one's for email, and the other is for data:
=ArrayFormula(if(len($B$2:$B),iferror(vlookup($B$2:$B,{Sheet1!$B$2:$B,Sheet1!$A$2:$A},2,)),))
=ArrayFormula(if(len($B$2:$B),iferror(vlookup($B$2:$B,{Sheet1!$B$2:$B,Sheet1!$C$2:$E},{2,3,4},)),))
