Is it possible to combine two IFS functions in one?

%3CLINGO-SUB%20id%3D%22lingo-sub-2336205%22%20slang%3D%22en-US%22%3EIs%20it%20possible%20to%20combine%20two%20IFS%20functions%20in%20one%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2336205%22%20slang%3D%22en-US%22%3E%3CP%3EHi!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20tring%20to%20combine%20these%20two%20functions%20in%20one%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIFS(F3%3D1%3BG3*plano_vendas!%24H%245%3BF3%3D2%3BG3*plano_vendas!%24I%245%3BF3%3D3%3BG3*plano_vendas!%24J%245)%3C%2FP%3E%3CP%3E%3DIFS(AND(F5%3D1%3BG5%3D0.5)%3B1*plano_vendas!K5%3BAND(F5%3D2%3BG5%3D0.5)%3B1*plano_vendas!L5%3BAND(F5%3D3%3BG5%3D0.5)%3B1*plano_vendas!M5)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2336205%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2336226%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20it%20possible%20to%20combine%20two%20IFS%20functions%20in%20one%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2336226%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1047245%22%20target%3D%22_blank%22%3E%40aigr_pt%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDepends%20on%20which%20logic%20do%20you%20mean.%20IFS()%20checks%20conditions%20one%20by%20one%20and%20stops%20evaluation%20on%20first%20TRUE%20condition%20ignoring%20the%20rest.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2336257%22%20slang%3D%22en-US%22%3ERe%3A%20Is%20it%20possible%20to%20combine%20two%20IFS%20functions%20in%20one%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2336257%22%20slang%3D%22en-US%22%3EYes%2C%20that%20is%20what%20is%20happening%20when%20I%20try%20to%20combine%20the%20two%20functions.%20What%20I%20need%20is%20to%20have%20the%20conditions%20in%20the%20first%20function%20and%20at%20the%20same%20time%2C%20when%20the%20value%20of%20F5%20is%201%2C%202%20or%203%20and%20G5%20is%200.5%2C%20I%20want%20to%20use%20the%20value%20that%20corresponds%20to%201%2C%202%20and%203%2C%20that%20is%20in%20the%20other%20sheet%2C%20to%20do%20the%20calculation.%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi!

 

I'm tring to combine these two functions in one:

 

=IFS(F3=1;G3*plano_vendas!$H$5;F3=2;G3*plano_vendas!$I$5;F3=3;G3*plano_vendas!$J$5)

=IFS(AND(F5=1;G5=0.5);1*plano_vendas!K5;AND(F5=2;G5=0.5);1*plano_vendas!L5;AND(F5=3;G5=0.5);1*plano_vendas!M5)

 

Is it possible?

3 Replies

@aigr_pt 

Depends on which logic do you mean. IFS() checks conditions one by one and stops evaluation on first TRUE condition ignoring the rest.

Yes, that is what is happening when I try to combine the two functions. What I need is to have the conditions in the first function and at the same time, when the value of F5 is 1, 2 or 3 and G5 is 0.5, I want to use the value that corresponds to 1, 2 and 3, that is in the other sheet, to do the calculation.
Well end up trying this and it works:

=IFS(AND(F3=1;G3=0.5);1*plano_vendas!K5;AND(F3=2;G3=0.5);1*plano_vendas!L5;AND(F3=3;G3=0.5);1*plano_vendas!M5;F3=1;G3*plano_vendas!$H$5;F3=2;G3*plano_vendas!$I$5;F3=3;G3*plano_vendas!$J$5)

Thanks