I need a function for column K of
Sheet1, that says:
"If the row of Sheet2 contains the same RID number as a row in Sheet1, then fill Sheet1 with the email address located in column K of Sheet2"
Assistance is greatly appreciated.
Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.Sign up to join this community
You have two Sheets, does this mean you have to separate Google Spreadsheets or you have two Sheets in one Google Spreadsheet? Both would require two sheets (tabs at the bottom) if you are pulling from an entire different sheet, use the =ImportRange("SHEET ID", "Range") function in the second tab to get the data.
As for the specifics of the question.
This is formula that you will use in column K in Sheet1
=IF(Sheet2!A1 = A1, Sheet2!K1, "")
This is assuming if course that your RID is located in the A column of the second sheet, if not change accordingly. You will also need to change the number if it is not starting on the first row.
The If function takes three parameters each separated by a comma, the first is called a condition. If the thing in the first parameter is true (The cell A1 in Sheet2 is the same as the A1 cell in Sheet1) the function will return the middle parameter that we set to be the value of the K1 cell in Sheet2. The third parameter is what happens if the condition is not true, for simplicity, I had it return nothing.
If the data is not the same, use a VLOOKUP
=IFERROR(VLOOKUP(A1, Sheet2!A$1:K, 6, FALSE), "")
paste this in cell K2:
=IFERROR(ARRAYFORMULA(VLOOKUP(A2:A, Sheet2!A1:K, 11, 0)), )
IFERROR(, )- if an error occurs, output nothing
ARRAYFORMULA()- assures that whole formula will be automated across sheet from a primary cell (in other words you don't need to copy this formula per every row)
VLOOKUP(A2:A, Sheet2!A1:K, 11, 0)- takes range
A2:Aand looks for matches in
A1:K. if a match exists it will bring up the 11th column from
Sheet2. lastly that
0indicates that it won't end mixed up somewhere in the process of getting those email data