IndexMatch multiple function ignore 0

[Mandy_84]

Hi All,
Need a help in following. In the cell "BLANK" I have entered the formula Index Match (=INDEX(J7:N9,MATCH(A2,I7:I9,0),MATCH(B2,I3:J3,0)) to find the amount of French Language at B2 level, however when I look in the table it doesn't show the amount. What I need is to find an exact amount ignoring "0". Also, keep in mind that A2,B2 can changed, also while changing J3. the table (J7:N9) is changing accordingly. Hope I made it understandable ))))

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1		BLANK
2	French	B2
3									Level	B2
4
5
6										A2	B2	B2	C1	C2
7									French	0	0	200	0	0
8									Spanish	0	0	300	0	0
9									German	0	0	200	0	0

<tbody>
</tbody>

Thanks a million times!

 

[steve the fish]

Provided the example is representative and the countries in the countries column are always unique this should work:

=SUMIFS(INDEX(J7:N9,MATCH(A2,I7:I9,0),0),J6:N6,B2)

 

[Mandy_84]

Thanks, but i need that formula recognizes, in case I change the level to C2 or change the language from French to German for example. so I will not have French B2. instead German C2

 

[steve the fish]

Did you not try it? Thats what it does.

 

[Mandy_84]

yes, it gives me "FALSE"

 

[steve the fish]

FALSE?? What is the formula you used? That formula cant produce false under normal circumstances. The only way i can think of is formatting of the cell its housed in.

 

[Aladin Akyurek]

You have B2 twice in I6:N6. Is this correct?

 

[Mandy_84]

Aha,nope,sorry, should be B1 and B2. Will try to fix this

 

[Aladin Akyurek]

Aha,nope,sorry, should be B1 and B2. Will try to fix this

Row\Col	A​	B​	C​	I​	J​	K​	L​	M​	N​
1​
2​	French	B2	200
3​
4​
5​
6​					A2	B1	B2	C1	C2
7​				French	0	0	200	0	0
8​				Spanish	0	0	300	0	0
9​				German	0	0	200	0	0
10​

In C2 enter:

=VLOOKUP(A2,$I$6:$N$9,MATCH(B2,INDEX($I$6:$N$9,1,0),0),0)

 

[Mandy_84]

Fantastic!!!!! thanks a lot!!!