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. Lett me know if you have any questions. I know this is a complicated ask.

Workbook Sample for Formula.xlsx

It's not a training assignment, it's real. I don't have the skills to write the formula I need on my own right now. Below is a start. It works on most of the Vendors on the Data tab. However, it doesn't work if the same Vendor is on the corresponding Project ID tab more than once. It also doesn't work if the Invoice Amount Total in column E of the Project ID tab is $0, it returns a result of $0. (The row on the Project ID tab of the Invoice Amount Total in column E is the same row as the Totals (Jan-Dec) header in column A.) I didn't include Invoice Amount Total as a header for this ask because it didn't seem relevant. I'm not sure why the formula would result in $0 if the Vendor and Project ID match.

=INDEX(INDIRECT("'"&D2&"'!b1:b500"),XLOOKUP("*"&B2&"*",INDIRECT("'"&D2&"'!A1:A500"),ROW(INDIRECT("'"&D2&"'!b1:b500")),,2)+13,1)

Yes, sorry about that. I wasn't sure which one made the most sense to put this in. Thanks!