0

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.

  • are you still seeking an answer? – user0 Jan 5 at 7:09
0

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), "")
  • Hi Mark. Thank you very much for your help. I copied down the following formula but with no positive results =IF(Sheet2!A2 = A2, Sheet2!K2, ""). I made this video to show the attempt screencast.com/t/eGIqbiXi7ci – tedpenner Nov 6 '18 at 3:28
  • Oh, didn't realize they were not identical data sets. Just use this formula instead =IFERROR(VLOOKUP(A1, Sheet2!A$1:K, 6, FALSE), "") I need the reputation, so if this works, please accept the answer. – Mark Shamy Nov 6 '18 at 3:49
  • Okay. I'll check it tomorrow. It is 10 p.m. here and I'm about brain dead – tedpenner Nov 6 '18 at 3:56
  • Still no luck. Here were my results screencast.com/t/478SDABgIp – tedpenner Nov 6 '18 at 18:26
0

paste this in cell K2:

=IFERROR(ARRAYFORMULA(VLOOKUP(A2:A, Sheet2!A1:K, 11, 0)), )

9

  • 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:A and looks for matches in Sheet2 on range A1:K. if a match exists it will bring up the 11th column from Sheet2. lastly that 0 indicates that it won't end mixed up somewhere in the process of getting those email data

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.