CODEWITHSUNDEEP
google-sheets
aglasier
aglasier

Reputation: 43

Grab data from certain cells in a row based on cell value in another sheet (Google Sheets)

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

Upvotes: 1

Views: 87

Answers (1)

NightEye
NightEye

Reputation: 11184

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:

Formula (D2):

=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},)),))

Sample Data (Sheet1):

sample

Output (Sheet2):

output

If not, then you'd have to separate the formula into 2, one's for email, and the other is for data:

C2:

=ArrayFormula(if(len($B$2:$B),iferror(vlookup($B$2:$B,{Sheet1!$B$2:$B,Sheet1!$A$2:$A},2,)),))

E2:

=ArrayFormula(if(len($B$2:$B),iferror(vlookup($B$2:$B,{Sheet1!$B$2:$B,Sheet1!$C$2:$E},{2,3,4},)),))

Output:

output

Upvotes: 1

Related Questions