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 ))))
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[TD]L[/TD]
[TD]M[/TD]
[TD]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]BLANK[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]French[/TD]
[TD]B2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Level[/TD]
[TD]B2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]A2[/TD]
[TD]B2[/TD]
[TD]B2[/TD]
[TD]C1[/TD]
[TD]C2[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]French[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]200[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Spanish[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]300[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]German[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]200[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
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

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

I​

[/td][td]

J​

[/td][td]

K​

[/td][td]

L​

[/td][td]

M​

[/td][td]

N​

[/td][/tr][tr][td]

1​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

2​

[/td][td] French[/td][td] B2[/td][td] 200[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

3​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

4​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

5​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][/tr]
[tr][td]

6​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] A2[/td][td] B1[/td][td] B2[/td][td] C1[/td][td] C2[/td][/tr]
[tr][td]

7​

[/td][td] [/td][td] [/td][td] [/td][td] French[/td][td] 0[/td][td] 0[/td][td] 200[/td][td] 0[/td][td] 0[/td][/tr]
[tr][td]

8​

[/td][td] [/td][td] [/td][td] [/td][td] Spanish[/td][td] 0[/td][td] 0[/td][td] 300[/td][td] 0[/td][td] 0[/td][/tr]
[tr][td]

9​

[/td][td] [/td][td] [/td][td] [/td][td] German[/td][td] 0[/td][td] 0[/td][td] 200[/td][td] 0[/td][td] 0[/td][/tr]
[tr][td]

10​

[/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td] [/td][td][/td][/tr]
[/table]

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!!!