Lookup In Multiple Sheets & Add Unique Identifier | Google Sheets

I'm trying to create an email lookup worksheet that essentially lists the results of a search throughout multiple worksheets (all in the same Sheet document).

As an example, I have "Sheet 1", "Sheet 2" and "Sheet 3". Each of these hold a log of our emails that staff send - helping us keep track of who sent what and to who. Sheet 1 belongs to one staff member, Sheet 2 belongs to another, etc.

On the master email lookup worksheet; you can put in the recipient's email address to find what emails had been sent to that person, and by who (from our staff). In each sheet the columns are:

• Column A: Email Date Sent
• Column B: Email Date Sent (different formatting)
• Column C: Email To (Recipient)
• Column D: Email Subject
• Column E: Web Link (URL)
• Column F: Internal notes

Is there a way to do this via a formula that looks over all of these worksheets, and also potentially adds in another column as a unique identifier for our staff members (e.g. if from "Sheet 1", add a column saying "Person 1", etc.)?

The dashboard lets us put in the recipient email, and it gives data back of Column 1: Date Sent, Column 2: Staff Member (unique identifier / name), and Column 3: Email Subject.

Example workbook:
docs(dot)google(dot)com/spreadsheets/d/1mNJRXqAynJaQKHDeyFqvlV1ExUXbnTZFv0YYu7dKhaU/edit?usp=sharing

it is likely possible to do exactly what you want, but without a spreadsheet with some sample data that is representative of what you want to do, it is impossible to give a definitive answer.

while i take a look at the file, here is the clickable link for others interested in helping:

https://docs.google.com/spreadsheets...haU/edit#gid=0

note to Dave Shopping: sorry, at the moment your post count is below 10, so the forum wont allow you to post links yet.. but hang in there.. once you pass 10 posts then you will be able to post links.... its a security measure to stop spammers from posting their junk

ok.. after having a look... thinking of formula solutions....

some things to consider/clarify/discuss:

will your sheet names always be named with a pipe symbol and a space before the name of the person?
if not, then can you rename the sheets in the manner they would be done in your real life situation?

can the Staff member column on the Email Lookup sheet be moved (so like switching around columns E and F) or must it be in this order/arrangement?

will the Staff member column always only use the first name AND it will be unique AND it will always be a part (after the pipe space) of the sheet name?

is this all that you will require to be done? or is there some chance that you will want more columns of information included in the results?

ok, with a bit of trickery, we can do this with QUERY, like this...

change the order of your columns to be:
Date, Email Subject, API URL Link, Staff Member

then put this formula into cell D7:

Please Login or Register  to view this content.

to explain....

we are doing a QUERY of each sheet, and Col1 is B, Col3 is D, and Col4 is E, then we slap the name into the last column..

BUT.. what if someone doesnt send an email.. when we slap the name on the end of the array, it creates an issue were we have missing values (the first 3 cells)...
so what we do is create some x cells to fill when theres an error (no data for the range in question)....

then we need to strip away the blank and x rows.... leaving us with the filtered data.

CAVEAT:
with this method, if someone sends 2 emails to the same person, it will be an error, so the data will be blanked out just like the error when there is no result found.

Sooooo... you can only use this if you know each person only sends 1 email to the contact.

after a bit of playing around.. heres a formula where you will not need to move any columns around....

again, drop this into D7 (just like before.. but dont move any columns around.. keep it just like you have it already)

Please Login or Register  to view this content.

in this version, we are creating Col 5 as an array of the persons name.. and slotting it into the middle of the QUERY data.

the benefit of this formula is that users can send more than 1 email to an email address, and it wont be errored out... so if someone send s 2 emails to the same person.. you will get 2 results for that person instead of none.