Compare multiple spreadsheets and print a column when a match is made

[redrowen]

Hello everyone!

I'm working on a project and I am wondering if someone could lend a hand on something.

I have two Excel spreadsheets, we will call them LM & EMP. In Column C of LM I have a list of E-Mail addresses. In Column I of EMP I also have a list of E-Mail addresses.

What I need is to compare the e-mail address in LM and EMP and if a match is found, print what is located in column K into an empty column.

So basically if C (LM) = I (EMP) Print K (EMP) into J (LM)

If anybody could help I would be forever greatful!

Thanks,

Red

[Trebor76]

Hi Red,

Assuming the data starts in cell J2 of the "LM" tab, try this in that cell and copy down as required (just change the cell references to the "EMP" tab to suit):

=IFERROR(VLOOKUP(C2,EMP!$I$2:$K$100,3,FALSE),"")

Regards,

Robert

[redrowen]

Hello Trebor,

The data I need to copy starts in J2 of the emp tab. I need to copy it to any column in lms.

Would this still apply?

[Trebor76]

In your original post you said that you wanted the data from column K if the email address matched. Which column is it?

Also, if you must cross-post, please at least make reference to other the forum(s) as I've done with this link.

Robert

[redrowen]

Oh thank you, I didn't realize about the cross posting. I can and will do that for any further posts. I also mis posted on the front page and will delete that post to maintain confluence.


Allow me to re-explain again to avoid confusion.

Workbook "LMS" contains E-Mail addresses to users and specific data they have completed for me.
Workbook "EMP" contains E-Mail addresses and also locations for the users.

My goal is to compare E-Mail addresses and when a match is made write that users location into the "LMS" workbook.

LMS:
E-Mail -> Column C
Blank Cell -> Column K

EMP:
E-Mail -> Column I
Location -> Column J

I need to compare LMS (C) to EMP (I) and print the data from EMP (J) to LMS (K)

Thanks,

[Trebor76]

What are the tab names from each workbook where the email addresses reside?

[redrowen]

LMS is named: Email
EMP is named: EMP_EMAIL_ADR

[Trebor76]

OK, make sure you have both workbooks open in the same session of Excel and then put this formula...

=IFERROR(VLOOKUP(C2,[EMP.xlsx]EMP_EMAIL_ADR!$I$2:$J$100,2,FALSE),"")

...into cell K2 of the "Email" tab within the "LMS" workbook (just change the last row number from 100 to match the actual last row from column J of the "EMP_EMAIL_ADR" tab and, if necessary, the extension of the "EMP" file) and copy down as required.

Note that if an exact match is not found, a blank cell (null) will be returned and that for large datasets it may slow down Excel.

HTH

Robert

[redrowen]

Hello Robert,

I will test this out when I get home, i'm just leaving the office now.

Will report back if the solution works and mark as Solved.

Thank you so much for taking the time.

[redrowen]

Hi Robert,

I was able to fix my problem with:

=IFERROR(VLOOKUP(C2,EMP!$I$2:$J$887,2,FALSE),"Not found")

Thank you so much for your help, you pointed me through everything I needed!

However, this does bring up one question. Is there a formula to automate C2 onward for the script? The spreadsheet I am working on has a good chunk of rows and it seems I may be missing something in not going manually down the column filling in the formula.

Thanks,

[Trebor76]

Is there a formula to automate C2 onward for the script

Once entered, you just need to copy the formula down to the last row in column K.

[redrowen]

Hey Trebor,

When I try that it just keeps repeating the same results, since each line the E-Mail address is different (C2/C3/C4, etc). I have to manually change the formula each time in order for it to work properly.

[Trebor76]

If you've copied down the formula and the cell reference has changed accordingly (i.e. C2 to C3 to C4...) then I'd say the calculation method is set to manual.

Either set it to automatic (Formulas > Calculation Options) or if you have to leave it on manual (which I wouldn't recoommend) press F9.

[redrowen]

Interesting, I checked and the calculations are set to automatic.

My formula at the moment is:
=IFERROR(VLOOKUP(C2,EMP!$I$2:$J$887,2,FALSE),"Not found")

I am pasting the same formula down the column, and the results are always the same as they were in C2. I have to manually update the C2 to C3 etc for the data to complete properly.

Is it possibly due to any changes I made to the formula from before?

Note, may be slow to reply -- off to bed.

Thanks again for all your help so far.

[Trebor76]

Seems odd. Make sure there are no circular references (if there are "Circular" will be displayed in the status bar) in either workbook as these could freeze (stop) the calculation engine from working.

[redrowen]

Was a user mistake, auto formatting works perfectly Trebor.

Thanks again!!

[Trebor76]

works perfectly Trebor. Thanks again!!

Thanks for letting us know and you're welcome