Write formula to compare planned amounts by vendor in one sheet to approved amounts by ven

Full post title: Write formula to compare planned amounts by vendor in one sheet to approved amounts by vendor in another tab based on project ID


I have an invoice reconciliation workbook where the first sheet (Data) is a list of the Planned Amounts by Vendor and Project ID. All of the other sheets in the workbook are broken out and named by the Project ID. I'm trying to compare the Planned Amounts by Vendor on the Data sheet to the Total Approved Amounts by Vendor in the rest of the workbook, based on the Project ID to ensure that they match.

All bolded fields in the attached workbook are headers that will not change. The positioning of the Vendors on the Project ID tabs may change based on the Partner Type (highlighted in red for easier visualization), but all Vendors will be in column A and all Total Approved Amounts will be in column B. Please note, some Vendors on the Project ID tabs have a Parent Company in parentheses on the Project ID tabs. The Vendors on the Data tab will not include the Parent Company.

Lastly, sometimes the same Vendor is listed twice on a Project ID based on the Partner Type. When this happens, it would be great if the result could say something like "Multiple Approved Amounts Found" so I know to double-check the Project ID tab to see which Approved Amount on the Data sheet aligns to the Total Planned Amount on the corresponding Project ID tab. Note, the Initiatives are included only on the Data tab of the workbook to help me distinguish this.

Please see the workbook attached. Let me know if you have any questions. I know this is a complicated ask.

Workbook Sample for Formula.xlsx