Using Index Match and an array formula for isolating error(s) between two data sets

[senoritacoconuts]

I am trying to easily reconcile two different accounting reports: one from our local system, and one from our accounts payable department. I've copied and pasted the data into a sheet as follows and am trying to determine where there are discrepancies between them:

From our local system (lines 2-1923)
Column B - Invoice #
Column C - Account
Column D - Project
Column E - Fund Total

From our accounts payable system (lines 2-1820)
Column I - Invoice #
Column J - Account
Column K - Project
Column L - Fund Total

In cell M2, I created the following array formula:
{=INDEX($E$2:$E$1923,MATCH(I2&J2&K2&L2,$B$2:$B$1923&$C$2:$C$1923&$D$2:$D$1923&$E$2:$E$1923,0))}

For example, the data in cells B2-E2 (which is one accounting 'string') must match on a corresponding row or 'string' in columns I-L exactly. My formula above works well, in that I get an "#N/A" in column M when there is an error found. However, ideally I would like a formula perhaps in multiple cells (one for each data element), that would show where the error is. In other words, what exactly is incorrect, the invoice number, or the account, or the project? Any helpful suggestions? I've been trying unsuccessfully over the last few weeks and am finally turning to you guys for help!

 

[Eric W]

Welcome to the forum!

Here's one (lengthy!) possibility:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		Invoice #	Account	Project	Fund Total				Invoice #	Account	Project	Fund Total
2		1	11	A	10				1	11	A	10	10
3		2	22	B	20				2	22	B	20	20
4		3	33	C	30				3	33	C	35	#N/A	Fund total does not match, or more than one component does not match
5		4	44	D	40				4	444	D	40	#N/A	Account=44
6		5	55	E	50				5	55	EE	50	#N/A	Project=E
7		6	66	F	60				6	666	FFF	60	#N/A	Fund total does not match, or more than one component does not match
8		7	77	G	70				71	77	G	70	#N/A	Invoice=7
9		8	88	H	80				42	99	I	90	#N/A	Invoice=9
10		9	99	I	90								0
11		10	99	I	90								0
12

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Array Formulas Cell Formula N2 {=IF(NOT(ISERROR(M2)),"",IFERROR("Invoice="&INDEX($B$2:$B$1923,MATCH(J2&"|"&K2&"|"&L2,$C$2:$C$1923&"|"&$D$2:$D$1923&"|"&$E$2:$E$1923,0)),IFERROR("Account="&INDEX($C$2:$C$1923,MATCH(I2&"|"&K2&"|"&L2,$B$2:$B$1923&"|"&$D$2:$D$1923&"|"&$E$2:$E$1923,0)),IFERROR("Project="&INDEX($D$2:$D$1923,MATCH(I2&"|"&J2&"|"&L2,$B$2:$B$1923&"|"&$C$2:$C$1923&"|"&$E$2:$E$1923,0)),"Fund total does not match, or more than one component does not match"))))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

The table is as you describe, and your formula in in column M. I added the array formula in N2, and copied down. It essentially looks for a match using Account/Project/Fund, and if it finds it, lists the matching Invoice. If it doesn't find it, tries with Invoice/Project/Fund, if that's not found, tries with Invoice/Account/Fund.

It's not perfect. Notice in N9 it says it should be Invoice 9, but if you look at the original table, there are actually 2 line items that match, excluding Invoice, 9 and 10.

But it should help! Let me know.

 

[senoritacoconuts]

Thanks so much, Eric! I'm going to give this a try first thing tomorrow morning and will report back. Regardless of the outcome, I appreciate your help!

 

[senoritacoconuts]

Good Morning, Eric. So I tried your formula and it worked just as you suspected it would. It pinpoints exactly where the error is, which is more than I'd expected actually. What I was originally contemplating as a solution was separate "match" columns for each field invoice, account, project, fund total where an "#N/A" would appear if there was no match for that field. Thanks again for your elegant solution!

 

[Eric W]

My formula does exactly what you contemplated, it just bundles 3 INDEX/MATCH combinations in a single formula and tries them sequentially.

Glad I could help!