INDEX / MATCH using two tables

Hello,

So I have two tables and one is pulling information from the other.  Here are the two tables:

Table 1

	A	B	C	D	E	F	G
1	ACCOUNTNUMBER	Name1	Name2	Name3	1 time charge	Fee	Taxes
2	1111111	John	Joe	Noe
3	22222222	Sam	Smith	Now

Table 2

	A	B	C
1	ACCOUNTNUMBER	Charge Type	Total Amount
2	1111111	1 time charge	$35.00
3	1111111	Fee	$29.98
4	1111111	Taxes	$0.00
5	22222222	1 time charge	$0.00
6	22222222	Fee	$29.98
7	22222222	Taxes	$0.00

 

When I put in this formula in E2 of the first table:

=INDEX('TABLE 2'!$C$2:$C$7,MATCH(Sheet1!$A2,'TABLE 2'!$A$2:$A$7,0),MATCH(Sheet1!E$1,'TABLE 2'!$B$2:$B$7,0))

I get the right result witch is $35.00

 

But when I put this formula in F2 of the first table:

=INDEX('TABLE 2'!$C$2:$C$7,MATCH(Sheet1!$A2,'TABLE 2'!$A$2:$A$7,0),MATCH(Sheet1!F$1,'TABLE 2'!$B$2:$B$7,0))

I get #REF!

What am I donig wrong?  Is there a limit to the number of times you use a index/match in a workseet? 

 

Thanks

Try this...

Entered in E2:

=SUMIFS('Table 2'!$C$2:$C$7,'Table 2'!$A$2:$A$7,$A2,'Table 2'!$B$2:$B$7,E$1)

Copy across to G2 then down as needed.

--

Biff

Microsoft Excel MVP

The INDEX formula requires a row no and a column no. However, you are passing a 1-column array to it (C2:C7), so the column no is always 1.

Try using this formula instead in Table 1

=SUMPRODUCT(--(Sheet2!$A$2:$A$7=Sheet1!$A2),--(Sheet2!$B$2:$B$7=Sheet1!E$1),Sheet2!$C$2:$C$7)

This finds all rows in Table 2 A2:A7 that match the account no given in cell $A2, as an array of 1 (for true) and 0 for false

and all rows in Table 2 B2:B7 that match the charge type given in cell E$1, as an array of 1 (for true) and 0 for false

and multiplies by the values in Table 2 C2:C7

Ed Ferrero

www.edferrero.com

For a dual criteria to match and where your source table is in multiple columns layout (your Table 2 as posted)

you could use something like this (for your F2 formula), press normal ENTER to confirm will do:

=INDEX('Table 2'!$C$2:$C$7,MATCH(1,INDEX(($A2='Table 2'!$A$2:$A$7)*(F$1='Table 2'!$B$2:$B$7),),0))

Adapt/use the above for your formula in E2 as well

(your formula as posted seems incorrect)