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

 

[63falcondude]

Paste the above data first. Then try to get the formula working on that sample data before you try to get it working on your actual data. Also, make sure to hit Ctrl+Shift+Enter instead of Enter.

 

[williams_ryan_1]

I copy and pasted your sample data into a workbook and everything worked! I also tried my old formula (pasted below) on your sample and it works as well. But neither your new formula or my old formula will work on the actual invoice. When I try your formula I get the #N/A error and when I try my old formula it just comes back blank with nothing in the cell. I'm sorry for the back and forth and I really appreciate your help! Let me know if this information helps you at all.

=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]

I copy and pasted your sample data into a workbook and everything worked! I also tried my old formula (pasted below) on your sample and it works as well. But neither your new formula or my old formula will work on the actual invoice. When I try your formula I get the #N/A error and when I try my old formula it just comes back blank with nothing in the cell. I'm sorry for the back and forth and I really appreciate your help! Let me know if this information helps you at all.

=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)),"")

That doesn't help me. I need to see your data or at least a sample of it in order to help. What I mean by this is not for you to explain it in words again but for you to post a table of sample data from each sheet here with the desired outcomes.

 

[williams_ryan_1]

Ok let me try this again. Sheet1 is the June invoice that I have already completed and have the line numbers listed in column H. Now I receive the July invoice with no line numbers (column H is blank). I paste the July invoice into Sheet2. I need a formula for Sheet2, cell H2, that will pull the line numbers in from Sheet1 by comparing the ChargeNumber (A), WorkLocation (B), and Description (C). There may be some new charges on Sheet2 but it's ok if those come back blank. However the majority (100+) of lines are used every month and it's so much easier to have the line numbers pulled over to Sheet2 rather than comparing line by line for each invoice that comes in. Let me know if this helps.

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

Sheet2
ChargeNumber / WorkLocation / Description / Code / BillRate / Hours / Sum of Cost / Line #
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

 

[63falcondude]

Try 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$100000,MATCH(Sheet2!A2&Sheet2!B2&Sheet2!C2,Sheet1!$A$2:$A$100000&Sheet1!$B$2:$B$100000&Sheet1!$C$2:$C$100000,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

I made the ranges from row 2 to row 100,000. You can change them if you need to.

 

[williams_ryan_1]

Thank you!

 

[63falcondude]

Thank you!

You're welcome. Did that work for you?

 

[williams_ryan_1]

Yes it's working again! Thanks again!

 

[63falcondude]

Yes it's working again! Thanks again!

Great, glad to help.