Iferror formula help

[williams_ryan_1]

Hello. I need help creating a formula to compare and look for items between sheet1 and sheet2 below. Sheet1 is a report I can run to see what lines currently exist in our system. Sheet2 is an example of what the monthly invoice looks like. I need to determine what the line numbers are (from Sheet1) for each line item on Sheet2. What formula can I use to compare the description, charge number and work location to populate the line # from sheet1 next to each line item on sheet2? Sheet2 is a pivot table if that makes a difference. If you could help that would be great!

SHEET1
Line # Description Charge number Funded Expended Remaining
1 WORKER 1 C12345 $126,426.45 $2,463.00 $123,963.45
2 WORKER 2 C23456 $4,783.52 $4,783.52 $-
3 WORKER 3 C34567 $921.96 $921.96 $-
4 WORKER 4 C45678 $866.40 $866.40 $-
5 WORKER 5 C56789 $35,208.40 $35,208.40 $-
160 WORKER 8 C78901 $1,397.60 $1,397.60 $-
8 WORKER 1 C89012 $5,915.91 $5,915.91 $-
9 WORKER 9 C90123 $980.10 $980.10 $-
162 WORKER 4 CA2345 $1,732.80 $1,732.80 $-
163 WORKER 8 CA2345 $7,056.72 $7,056.72 $-
164 WORKER 10 CA2345 $1,842.88 $1,842.88 $-

SHEET2 (Pivot Table)
ChargeNumber WorkLocation Description Code BillRate Hours Sum of Cost
C12345 On-Site WORKER 8 1000 $153.66 114 $17,517.24
C23456 Off Site WORKER 4 2000 $60.17 39 $2,346.63
C34567 On-Site WORKER 8 1000 $153.66 4 $614.64
C56789 On-Site WORKER 1 3000 $118.83 25 $2,970.75
C67890 On-Site WORKER 2 4000 $87.35 32.5 $2,838.88
C67890 On-Site WORKER 3 3000 $111.23 44 $4,894.12
C45678 On-Site WORKER 5 3000 $102.35 159.5 $16,324.83
C78901 On-Site WORKER 8 1000 $153.66 8 $1,229.28
C89012 Off Site WORKER 10 5000 $98.01 4 $392.04
C90123 Off Site WORKER 10 5000 $98.01 24 $2,352.24
C90123 On-Site WORKER 3 3000 $111.23 129 $14,348.67
C01234 On-Site WORKER 1 3000 $118.83 4 $475.32
CA2345 On-Site WORKER 2 4000 $87.35 23.5 $2,052.73
CA1234 On-Site WORKER 2 4000 $87.35 64 $5,590.40

 

[williams_ryan_1]

I thought I had the columns formatted to see better than this but apparently the system removes the extra spaces I put in. Sorry for the jumbled mess.

 

[63falcondude]

Maybe something like this:

Excel 2010
	A	B	C	D	E	F
1	Line #	Description	Charge number	Funded	Expended	Remaining
2	1	WORKER 1	C12345	$126,426.45	$2,463.00	$123,963.45
3	2	WORKER 2	C23456	$4,783.52	$4,783.52	$0.00
4	3	WORKER 3	C34567	$921.96	$921.96	$0.00
5	4	WORKER 4	C45678	$866.40	$866.40	$0.00
6	5	WORKER 5	C56789	$35,208.40	$35,208.40	$0.00
7	160	WORKER 8	C78901	$1,397.60	$1,397.60	$0.00
8	8	WORKER 1	C89012	$5,915.91	$5,915.91	$0.00
9	9	WORKER 9	C90123	$980.10	$980.10	$0.00
10	162	WORKER 4	CA2345	$1,732.80	$1,732.80	$0.00
11	163	WORKER 8	CA2345	$7,056.72	$7,056.72	$0.00
12	164	WORKER 10	CA2345	$1,842.88	$1,842.88	$0.00
Sheet1

Excel 2010
	A	B	C	D	E	F	G	H
1	ChargeNumber	WorkLocation	Description	Code	BillRate	Hours	Sum of Cost	Line #
2	C12345	On-Site	WORKER 8	1000	$153.66	114	$17,517.24	Not Found
3	C23456	Off Site	WORKER 4	2000	$60.17	39	$2,346.63	Not Found
4	C34567	On-Site	WORKER 8	1000	$153.66	4	$614.64	Not Found
5	C56789	On-Site	WORKER 1	3000	$118.83	25	$2,970.75	Not Found
6	C67890	On-Site	WORKER 2	4000	$87.35	32.5	$2,838.88	Not Found
7	C67890	On-Site	WORKER 3	3000	$111.23	44	$4,894.12	Not Found
8	C45678	On-Site	WORKER 5	3000	$102.35	159.5	$16,324.83	Not Found
9	C78901	On-Site	WORKER 8	1000	$153.66	8	$1,229.28	160
10	C89012	Off Site	WORKER 10	5000	$98.01	4	$392.04	Not Found
11	C90123	Off Site	WORKER 10	5000	$98.01	24	$2,352.24	Not Found
12	C90123	On-Site	WORKER 3	3000	$111.23	129	$14,348.67	Not Found
13	C01234	On-Site	WORKER 1	3000	$118.83	4	$475.32	Not Found
14	CA2345	On-Site	WORKER 2	4000	$87.35	23.5	$2,052.73	Not Found
15	CA1234	On-Site	WORKER 2	4000	$87.35	64	$5,590.40	Not Found
Sheet2

Cell Formulas
Range	Formula
H2	{=IFERROR(INDEX(Sheet1!$A$2:$A$12,MATCH(Sheet2!C2&Sheet2!A2,Sheet1!$B$2:$B$12&Sheet1!$C$2:$C$12,0)),"Not Found")}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[63falcondude]

I did not include WorkLocation in the formula since you did not have it in your example in Sheet1.

 

[williams_ryan_1]

You brought up a good point. I'm not going to be able to match the WorkLocation since that doesn't pull up in my report. Ok what if we just matched last month's invoice with this months? So how would I match Sheet2 to another example of a Sheet2 (like the month prior)? All the same columns would exist if that makes sense. I have done this in the past with the below formula but it isn't working anymore for some reason? I am guessing they changed their formatting in excel? Or maybe the pivot tables have something to do with it?

=IFERROR(INDEX(Sheet1!$H$2:$H$1000,MATCH(1,(Sheet1!$A$2:$A$1000=$A2)*(Sheet1!$B$2:$B$1000=$B2)*(Sheet1!$D$2:$D$1000=$D2),0)),"")

 

[63falcondude]

Can you create a small example of what you have along with the desired results?

 

[williams_ryan_1]

Sorry I should have been more specific. So what we would have is a Sheet1, which is the same format as my original Sheet2 example. The only difference is that there would be one more additional column (H) with the line numbers. So Sheet1 in this scenario would be the June invoice. Now I receive the July invoice which I would copy in as Sheet2. It is just like Sheet1 except that column H is empty. I need a formula for Sheet2 that pulls in the line numbers from Sheet1 column H. Does this make sense?

 

[63falcondude]

Something like this?

Excel 2010
	A	B	C	D	E	F	G	H
1	ChargeNumber	WorkLocation	Description	Code	BillRate	Hours	Sum of Cost	Line #
2	C12345	On-Site	WORKER 8	1000	$153.66	114	$17,517.24	123
3	C34567	On-Site	WORKER 8	1000	$153.66	4	$614.64	789
4	C78901	On-Site	WORKER 8	1000	$153.66	8	$1,229.28	555
5	C23456	Off Site	WORKER 4	2000	$60.17	39	$2,346.63	456
6	C56789	On-Site	WORKER 1	3000	$118.83	25	$2,970.75	111
7	C67890	On-Site	WORKER 3	3000	$111.23	44	$4,894.12	333
8	C45678	On-Site	WORKER 5	3000	$102.35	159.5	$16,324.83	444
9	C90123	On-Site	WORKER 3	3000	$111.23	129	$14,348.67	888
10	C01234	On-Site	WORKER 1	3000	$118.83	4	$475.32	999
11	C67890	On-Site	WORKER 2	4000	$87.35	32.5	$2,838.88	222
12	CA2345	On-Site	WORKER 2	4000	$87.35	23.5	$2,052.73	101
13	CA1234	On-Site	WORKER 2	4000	$87.35	64	$5,590.40	102
14	C89012	Off Site	WORKER 10	5000	$98.01	4	$392.04	666
15	C90123	Off Site	WORKER 10	5000	$98.01	24	$2,352.24	777
Sheet1

Excel 2010
	A	B	C	D	E	F	G	H
1	ChargeNumber	WorkLocation	Description	Code	BillRate	Hours	Sum of Cost	Line #
2	C12345	On-Site	WORKER 8	1000	$153.66	114	$17,517.24	123
3	C23456	Off Site	WORKER 4	2000	$60.17	39	$2,346.63	456
4	C34567	On-Site	WORKER 8	1000	$153.66	4	$614.64	789
5	C56789	On-Site	WORKER 1	3000	$118.83	25	$2,970.75	111
6	C67890	On-Site	WORKER 2	4000	$87.35	32.5	$2,838.88	222
7	C67890	On-Site	WORKER 3	3000	$111.23	44	$4,894.12	333
8	C45678	On-Site	WORKER 5	3000	$102.35	159.5	$16,324.83	444
9	C78901	On-Site	WORKER 8	1000	$153.66	8	$1,229.28	555
10	C89012	Off Site	WORKER 10	5000	$98.01	4	$392.04	666
11	C90123	Off Site	WORKER 10	5000	$98.01	24	$2,352.24	777
12	C90123	On-Site	WORKER 3	3000	$111.23	129	$14,348.67	888
13	C01234	On-Site	WORKER 1	3000	$118.83	4	$475.32	999
14	CA2345	On-Site	WORKER 2	4000	$87.35	23.5	$2,052.73	101
15	CA1234	On-Site	WORKER 2	4000	$87.35	64	$5,590.40	102
Sheet2

Cell Formulas
Range	Formula
H2	{=INDEX(Sheet1!$H$2:$H$15,MATCH(Sheet2!A2&Sheet2!C2,Sheet1!$A$2:$A$15&Sheet1!$C$2:$C$15,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[63falcondude]

That matches the Charge Number and Description and returns the appropriate Line #.

 

[williams_ryan_1]

For some reason I am getting the #N/A error in the cells after I paste the above formula and then press ctrl, shift, enter. I wish I could provide a screenshot but I'm not seeing where that is an option.