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.

 

[BigBeachBananas]

Which excel version or you using?

 

[Yvonne Ng]

Which excel version or you using?

guest should be as per attached.

 

[BigBeachBananas]

Put a + sign before the second INDEX, instead of a comma.

 

[Yvonne Ng]

In this way ?
=INDEX(Sept!G8:G10,MATCH(Summary!B10,Sept!E8:E10,0),MATCH(Summary!C10,Sept!F8:F10,0))+INDEX(Oct!G8:G10,MATCH(Summary!B10,Oct!E8:E10,0),MATCH(Summary!C10,Oct!F8:F10,0))

Its giving me an anawer :

#N/A​

 

[BigBeachBananas]

Look at your Sept sheet. Your invoice number doesn't have A in front, whereas Oct does.

 

[Yvonne Ng]

Look at your Sept sheet. Your invoice number doesn't have A in front, whereas Oct does.

Yes, because i want to make it as different invoice no for same client, which is my second criteria.
I might have the same client to be billed for sept and oct. but my invoice is different.
so in summary page, i suppose to get a return for sept when both B10 and C10 in summary page match, similiar for and B11 and C!1 for Oct.

 

[BigBeachBananas]

How about this?

Book1
	E	F	G
7	Client	Invoice	Amount
8	A	A123	10
9	E	A456	20
10	C	A678	10
11	A	123	40
12	E	456	50
13	C	678	30
Summary

Cell Formulas
Range	Formula
G8:G10	G8	=INDEX(Sept!$G$8:$G$10,MATCH(E8&F8,Sept!$E$8:$E$10&Sept!$F$8:$F$10,0))
G11:G13	G11	=INDEX(Oct!$G$8:$G$10,MATCH(E11&F11,Oct!$E$8:$E$10&Oct!$F$8:$F$10,0))

 

[Yvonne Ng]

my main purpose for the summary page is that I'm able to look up all the sheet on every month based on the client and invoice no given in summary.
the given formula in as shown can only look up one particular month only. but not the entire months throughout the years.
would there be a formula that based on column E and F criteria, and look up the months sheets and return the value to column G?

 

[BigBeachBananas]

my main purpose for the summary page is that I'm able to look up all the sheet on every month based on the client and invoice no given in summary.
the given formula in as shown can only look up one particular month only. but not the entire months throughout the years.
would there be a formula that based on column E and F criteria, and look up the months sheets and return the value to column G?

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.