Thread: If identical value found in two cells (different spreadsheets) then update main sheet

Hello all! I am VERY much looking forward to seeing if you might be able to help me with this... I spent about 4 hours at work trying to get this working and was totally unsuccessful with little progress. any help is VERY MUCH WELCOMED in advance!

I believe this is relatively simple for excel guru's, but I could be wrong. I even installed the plugin zlookup to do this, but couldn't get it working. Here is the scenario

Spreadsheet 1 (called Master.xls) and Spreedsheet 2 (called DailyReportData.xls) are seen below.

My goal is to say the following: If Master.xls has an identical cell in column A to a cell in Column A of DailyReportData.xls, for that row update column B in Master.xls using the data from Column B of DailyReportData.xls. Here is a sample using real data:



So if, for example, IF AN IDENTICAL IP ADDRESS EXIST IN BOTH MASTER.XLS'S COLUMN A AND DAILYREPORTDATA.XLS'S COLUMN A -- THEN TAKE THE 'LAST DATE REFERENCED' VALUE FROM DAILYREPORTDATA.XLS AND WRITE THAT TO MASTER.XLS so that the most recent data is in the master file.

This needs to be done for every identical IP address match found between the two sheets.

I have also attached a spreadsheet with the same data as above, formatted nicely.


Thank you so much in advance and if you need more information, please ask!

Azam,

With a little trickery, I was able to adapt this to work between spreadsheets and not just between tabs!! This is so wonderful. Thank you so much.

Here is the modification to your formula for those interested:

=IF(ISNA(VLOOKUP($F7,[Daily.xlsx]DailyReport!$C$2:$F$200,2,0))=TRUE,$L7,VLOOKUP($F7,[Daily.xlsx]DailyReport!$C$2:$F$200,4,0))
(The formula above -- mainly given to me by Azam -- works for different spreadsheets as opposed to different tabs. If you only want to modify different tabs, see his submission.

I hope that makes sense

Thank you so much for your time -- this was very important to me.

it is nice to read that it is wroking for you

Kindly note that first formula is using vlookup function and second formula is using index+match funcitons and both are giving same results

If your problem is solved, mark your thread as Solved: Click the Edit button on your first post in the thread, Click Go Advanced, select [SOLVED] from the Prefix dropdown, then click Save Changes.