Lookup between 2 tables and match the field and update the records

[manshah]

Hi

I have 2 tables as shown below. I want to look up the "Field" column in Table 2 and and update the record in Table 1 for the column mentioned in "Field column of Table 2 based on most current "System Notes Date".
For Example: In Table 1, for document number OPP1003, "Exp.Close Date" should be updated to reflect 10/1/2019. Please see the table "Result" which shows the final output.

TABLE 1:

Document Number Department Booking Date Sales Rep Exp. Close Date Opportunity Status Projected Total Probability Weighted Total Project Start Date Project End Date OPP1003 NE 1/23/2019 MD 7/1/2019 SP 14,500.00 0.4 5,800.00 9/6/2019 12/31/2019 OPP1041 SMB 5/20/2019 Marci Cap 7/1/2019 DNO 8700 0.2 1740 7/24/2019 7/24/2019 <colgroup><col><col><col><col><col><col><col><col span="4"></colgroup><tbody> </tbody>

<colgroup><col><col><col><col><col><col><col><col span="4"><col></colgroup><tbody>
</tbody>

TABLE 2:

Document Number	Date Created	System Notes Date	Projected Total	Field	Old Value	New Value
OPP1003	9/11/2018 10:35	7/24/2019 12:03	14500	Exp. Close Date	7/26/2019	8/30/2019
OPP1003	9/11/2018 10:35	7/24/2019 12:03	14500	Project Start Date	9/6/2019	9/20/2019
OPP1003	9/11/2018 10:35	7/30/2019 15:43	14500	Exp. Close Date	8/30/2019	10/1/2019
OPP1003	9/11/2018 10:35	7/30/2019 15:43	14500	Project Start Date	9/20/2019	10/31/2019
OPP1041	9/11/2018 19:53	7/18/2019 9:04	8700	Exp. Close Date	7/24/2019	8/6/2019
OPP1041	9/11/2018 19:53	7/18/2019 9:04	8700	Project End Date	7/24/2019	8/6/2019
OPP1041	9/11/2018 19:53	7/18/2019 9:04	8700	Project Start Date	7/24/2019	8/6/2019
OPP1041	9/11/2018 19:53	7/18/2019 9:05	8700	Probability	20	50
OPP1041	9/11/2018 19:53	7/18/2019 9:05	8700	Projected Total	8700.00	5220.00

<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

RESULT:

Document Number	Department	Booking Date	Sales Rep	Exp. Close Date	Opportunity Status	Projected Total	Probability	Weighted Total	Project Start Date	Project End Date
OPX1003	NE	1/23/2019	MD	10/1/2019	SP	14,500.00	0.4	5,800.00	10/31/2019	12/31/2019
OPX1041	SMB	5/20/2019	Marci Cap	8/6/2019	DNO	5220	0.5	2610	8/6/2019	8/6/2019

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

I have thousands of records of similar data and looking for formula or code to automate the process.

Any help is greatly appreciated. Thank you!