Thanks for reading my question!

I have two different datasets, each in a different sheet, as below:

1st table: Sales data from survey (less correct):

2nd table: Sales data from accounting (correct data):

Now, I have to make a summary report of these two. The rules are:

1. Each client from both lists must be included (I have accomplished this already; Column "Client");
2. I must include Yearly Sales; if the data from 2nd table is different from the 1st, the data in 2nd table is included;
3. I'm trying to accomplish this in a spilled range ("Client" is a spilled range);
4. There are unique client names in each table (they are all included in my summary report);
5. I'm trying to accomplish this without VBA code.

The summary report must look like this:

Note: I've tried using XLOOKUP and SUMIFS combined with IF statement but I think I'm missing something important because my conditions are not working.

CodePudding user response：

Wrap you lookup in IFERROR. Search in your accounting data first, if it throws an error search in the survey data:

Example: =IFERROR(VLOOKUP(G2:G5,D2:E4,2,0),VLOOKUP(G2:G5,A2:B4,2,0))

In this example: D2:E4 = accounting data, A2:B4 = survey data, G2:G5= unique names from both data sets.