Match 2 Criteria on multiple sheet and return a value

[Yvonne Ng]

I have data sheet namely Sept, Oct.
I want to create a summary page to see how much I've been bill on that particular invoice.
I try used index and match function for multiple sheet, but not successful.

Please illustrate and advise.
Attached file for reference.

 

[Yvonne Ng]

Depends on how many months you have. By your description, it's probably a lot. It is possible with a formula but can get pretty large. Easier solution would be VBA.

yes, if to compile a year, I might need a formula for every month from Jan to December.
i'm not so familiar with VBA as compare using formula.
Appreciates if you can advise further using formula.

 

[BigBeachBananas]

I would use helper columns for each month, then look for the one with real value like this.

Book1
	A	B	C	D	E	F
1	Client	Invoice	Sept	Oct	Nov	Amount
2	A	A123	10	0	0	10
3	E	A456	20	0	0	20
4	C	A567	30	0	0	30
5	A	123	0	40	0	40
6	E	456	0	50	0	50
7	C	678	0	60	0	60
8	A	B123	0	0	70	70
9	E	B456	0	0	80	80
10	C	B6789	0	0	90	90
Summary

Cell Formulas
Range	Formula
C2:E10	C2	=IFERROR(INDEX(INDIRECT("'" &C$1& "'!$C2:C5"),MATCH($A2&$B2,INDIRECT("'" & C$1 & "'!$A$2:$A$5") &INDIRECT("'" & C$1 & "'!$B$2:$B$5"),0)),0)
F2:F10	F2	=SUM(C2:E2)

Sample of the monthly sheet.

Book1
	A	B	C
1	Client	Invoice	Amount1
2	A	A123	10
3	E	A456	20
4	C	A567	30
Sept

 

[Yvonne Ng]

I would use helper columns for each month, then look for the one with real value like this.

Book1
	A	B	C	D	E	F
1	Client	Invoice	Sept	Oct	Nov	Amount
2	A	A123	10	0	0	10
3	E	A456	20	0	0	20
4	C	A567	30	0	0	30
5	A	123	0	40	0	40
6	E	456	0	50	0	50
7	C	678	0	60	0	60
8	A	B123	0	0	70	70
9	E	B456	0	0	80	80
10	C	B6789	0	0	90	90
Summary

Cell Formulas
Range	Formula
C2:E10	C2	=IFERROR(INDEX(INDIRECT("'" &C$1& "'!$C2:C5"),MATCH($A2&$B2,INDIRECT("'" & C$1 & "'!$A$2:$A$5") &INDIRECT("'" & C$1 & "'!$B$2:$B$5"),0)),0)
F2:F10	F2	=SUM(C2:E2)

Sample of the monthly sheet.

Book1
	A	B	C
1	Client	Invoice	Amount1
2	A	A123	10
3	E	A456	20
4	C	A567	30
Sept

I would use helper columns for each month, then look for the one with real value like this.

Book1
	A	B	C	D	E	F
1	Client	Invoice	Sept	Oct	Nov	Amount
2	A	A123	10	0	0	10
3	E	A456	20	10	0	0	10
3	E	A456	20	0	0	20
4	C	A567	30	0	0	30
5	A	123	0	40	0	40
6	E	456	0	50	0	50
7	C	678	0	60	0	60
8	A	B123	0	0	70	70
9	E	B456	0	0	80	80
10	C	B6789	0	0	90	90
Summary

Cell Formulas
Range	Formula
C2:E10	C2	=IFERROR(INDEX(INDIRECT("'" &C$1& "'!$C2:C5"),MATCH($A2&$B2,INDIRECT("'" & C$1 & "'!$A$2:$A$5") &INDIRECT("'" & C$1 & "'!$B$2:$B$5"),0)),0)
F2:F10	F2	=SUM(C2:E2)

Sample of the monthly sheet.

Book1
	A	B	C
1	Client	Invoice	Amount1
2	A	A123	10
3	E	A456	20
4	C	A567	30
Sept

[/QUOTE]

I would use helper columns for each month, then look for the one with real value like this.

Book1
	A	B	C	D	E	F
1	Client	Invoice	Sept	Oct	Nov	Amount
2	A	A123	10	0	0	10
3	E	A456	20	0	0	20
4	C	A567	30	0	0	30
5	A	123	0	40	0	40
6	E	456	0	50	0	50
7	C	678	0	60	0	60
8	A	B123	0	0	70	70
9	E	B456	0	0	80	80
10	C	B6789	0	0	90	90
Summary

Cell Formulas
Range	Formula
C2:E10	C2	=IFERROR(INDEX(INDIRECT("'" &C$1& "'!$C2:C5"),MATCH($A2&$B2,INDIRECT("'" & C$1 & "'!$A$2:$A$5") &INDIRECT("'" & C$1 & "'!$B$2:$B$5"),0)),0)
F2:F10	F2	=SUM(C2:E2)

Sample of the monthly sheet.

Book1
	A	B	C
1	Client	Invoice	Amount1
2	A	A123	10
3	E	A456	20
4	C	A567	30
Sept

I would use helper columns for each month, then look for the one with real value like this.

Book1
	A	B	C	D	E	F
1	Client	Invoice	Sept	Oct	Nov	Amount
2	A	A123	10	0	0	10
3	E	A456	20	0	0	20
4	C	A567	30	0	0	30
5	A	123	0	40	0	40
6	E	456	0	50	0	50
7	C	678	0	60	0	60
8	A	B123	0	0	70	70
9	E	B456	0	0	80	80
10	C	B6789	0	0	90	90
Summary

Cell Formulas
Range	Formula
C2:E10	C2	=IFERROR(INDEX(INDIRECT("'" &C$1& "'!$C2:C5"),MATCH($A2&$B2,INDIRECT("'" & C$1 & "'!$A$2:$A$5") &INDIRECT("'" & C$1 & "'!$B$2:$B$5"),0)),0)
F2:F10	F2	=SUM(C2:E2)

Sample of the monthly sheet.

Book1
	A	B	C
1	Client	Invoice	Amount1
2	A	A123	10
3	E	A456	20
4	C	A567	30
Sept

I'm unable to do this because I received the

I would use helper columns for each month, then look for the one with real value like this.

Book1
	A	B	C	D	E	F
1	Client	Invoice	Sept	Oct	Nov	Amount
2	A	A123	10	0	0	10
3	E	A456	20	0	0	20
4	C	A567	30	0	0	30
5	A	123	0	40	0	40
6	E	456	0	50	0	50
7	C	678	0	60	0	60
8	A	B123	0	0	70	70
9	E	B456	0	0	80	80
10	C	B6789	0	0	90	90
Summary

Cell Formulas
Range	Formula
C2:E10	C2	=IFERROR(INDEX(INDIRECT("'" &C$1& "'!$C2:C5"),MATCH($A2&$B2,INDIRECT("'" & C$1 & "'!$A$2:$A$5") &INDIRECT("'" & C$1 & "'!$B$2:$B$5"),0)),0)
F2:F10	F2	=SUM(C2:E2)

Sample of the monthly sheet.

Book1
	A	B	C
1	Client	Invoice	Amount1
2	A	A123	10
3	E	A456	20
4	C	A567	30
Sept

I'm unable to do this because I received different client with different invoice no., every month , that's why I have Multiple sheet .
and for summary , i have different sheet for individual client too. that's why i need set up a formula to retrieve the data on different month into different summary page.

 

[Yvonne Ng]

I'm unable to do this because I received the

I'm unable to do this because I received different client with different invoice no., every month , that's why I have Multiple sheet .
and for summary , i have different sheet for individual client too. that's why i need set up a formula to retrieve the data on different month into different summary page.
[/QUOTE]

It would be greatful if you can advise the formula to work on multiple sheet with the criteria which i mention earlier.

 

[BigBeachBananas]

It would be greatful if you can advise the formula to work on multiple sheet with the criteria which i mention earlier.

Did you look at the formula? I'm using INDIRECT to reference each monthly sheet.

For example, INDRECT(C1,...), where C1 = Sept. I'm looking at the Sept sheet.

 

[Yvonne Ng]

Did you look at the formula? I'm using INDIRECT to reference each monthly sheet.

For example, INDRECT(C1,...), where C1 = Sept. I'm looking at the Sept sheet.

yes, however, in my summary, i do not want to split monthly by monthly, i just need to have the amount to be reflected based on criteria client and Invoice match by looking into all sheets of different month.
as i have different sheet of summary for client A, cliemt B, client C. so for every of the summary sheet, i just need to know how much I;ve been invoice afterall.

 

[BigBeachBananas]

yes, however, in my summary, i do not want to split monthly by monthly, i just need to have the amount to be reflected based on criteria client and Invoice match by looking into all sheets of different month.

I said the monthly columns are helper columns to look into each sheet. As you potentially have 12 sheets, 1 formula would be massive. You can choose to hide those. The Amount column is the relevant one. Am I missing something?

 

[Yvonne Ng]

I said the monthly columns are helper columns to look into each sheet. As you potentially have 12 sheets, 1 formula would be massive. You can choose to hide those. The Amount column is the relevant one. Am I missing something?

 

[Yvonne Ng]

 

[Yvonne Ng]

View attachment 99964View attachment 99965View attachment 99966View attachment 99967View attachment 99968

I have 3 data base here, showing Sept, oct, Nov, and I have 3 seperate summary page for 3 different client. I want retreve the amount on different month sheet and input into client's summary.
this is exactly what i need