Index match formula with multiple criteria not working for me

[rosef93]

Hi there,

I am having no luck with my index match formula.

I am trying to create a formula that will match with the following criteria "Apple" and "FY19" to return the number 200. If anyone can help that would be really appreciated. Any suggestions on formulas?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Opportunity#[/TD]
[TD]Apple[/TD]
[TD]Apple[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD]FY19[/TD]
[TD]FY20[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]300[/TD]
[TD]200[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

 

[gaz_chops]

what is your formula?

 

[Ramballah]

Hi there,

I am having no luck with my index match formula.

I am trying to create a formula that will match with the following criteria "Apple" and "FY19" to return the number 200. If anyone can help that would be really appreciated. Any suggestions on formulas?

[TABLE="width: 500"]
<tbody>[TR]
[TD]Opportunity#[/TD]
[TD]Apple[/TD]
[TD]Apple[/TD]
[TD]Apple[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]Total[/TD]
[TD]FY19[/TD]
[TD]FY20[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]300[/TD]
[TD]200[/TD]
[TD]100[/TD]
[/TR]
</tbody>[/TABLE]

i mean why dont you just use the if + and formula?

 

[rosef93]

I don't think this will work will it? Am I able to return the value that is in the Total cell for Apple/FY19?

 

[Ramballah]

I don't think this will work will it? Am I able to return the value that is in the Total cell for Apple/FY19?

yes thats why i said if + and. =if(and(C1="apple",C2="FY19"),$C$6,"not true") or something

 

[rosef93]

=INDEX(A1:D6,MATCH(1,(A1:D1="Apple")*(A2:D2="FY19"),0),6)

 

[rosef93]

=INDEX(A1:D6,MATCH(1,(A1:D1="Apple")*(A2:D2="FY19"),0),6)

 

[gaz_chops]

Try

=INDEX(A6:D6,,MATCH(1,IF((A1:D1="Apple")*(A2:D2="FY19"),1),0))

Enter as an Array, Ctrl Shift & Enter

 

[steve the fish]

With INDEX rows come before columns so the 6 needs to be first as you need the 6th row. If its just a fixed 6 then your index range could just be A6:D6 then you just use your match formula with the index and the 6 wouldnt be required. The formula requires CTRL-SHIFT-ENTER. As it happens you could just use a SUMIFS the way your data is set up.

 

[steve the fish]

Heres 3 slightly different ones you could use:

=INDEX(B6:D6,MATCH(1,INDEX((B1:D1="Apple")*(B2:D2="FY19"),0),0))
=INDEX(B3:D6,MATCH("Total",A3:A6,0),MATCH(1,INDEX((B1:D1="Apple")*(B2:D2="FY19"),0),0))
=SUMIFS(B6:D6,B1:D1,"Apple",B2:D2,"FY19")