SOLVED

Multiple parameters for formula IF (with different cells)

%3CLINGO-SUB%20id%3D%22lingo-sub-2713960%22%20slang%3D%22en-US%22%3EMultiple%20parameters%20for%20formula%20IF%20(with%20different%20cells)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2713960%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20trying%20to%20compose%20a%20formula%20using%20%22IF%22%2C%20considering%20the%20complexity%20of%20using%20parameters%20informed%20in%26nbsp%3B%20two%20columns%20to%20defining%20the%20result%20in%20a%20third%20column(if%20true)%26nbsp%3B%20based%20on%20the%20selection%20of%20information%20between%20the%20two%20columns%20before.%3C%2FP%3E%3CP%3ESee%20the%26nbsp%3B%20example%26nbsp%3B%20bellow%3A%3C%2FP%3E%3CP%3EColumn%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Column%202%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3BValue%20(result)%3C%2FP%3E%3CP%3ECompany%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Project%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%24%20120%2C00%26nbsp%3B%3C%2FP%3E%3CP%3ECompany%201%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Presales%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%24%26nbsp%3B%20%26nbsp%3B90%2C00%3C%2FP%3E%3CP%3ECompany%202%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Project%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%24%20150%2C00%3C%2FP%3E%3CP%3ECompany%202%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20Presales%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%26nbsp%3B%20%24%20115%2C00%3C%2FP%3E%3CP%3EI'm%20trying%20to%20use%20the%20four%20lines%20to%20configure%20a%20formule%20to%20set%20the%20%22value%22%20automatically%2C%20according%20to%20the%20options%20selected%2C%20for%20example%3A%3C%2FP%3E%3CP%3E%26nbsp%3BIf%20the%20information%20selected%20in%20the%20column%201%20is%20company%201%20and%20de%20information%20selected%20in%20the%20column%202%20is%20Presales%2C%20the%20value%20has%20to%20be%20set%20automatically%20to%20%2490%2C00.%3C%2FP%3E%3CP%3E%26nbsp%3BIf%20the%20information%20selected%20in%20the%20column%201%20is%20company%202%20and%20de%20information%20selected%20in%20the%20column%202%20is%20Presales%2C%20the%20value%20has%20to%20be%20set%20automatically%20to%20%24115%2C00.%3C%2FP%3E%3CP%3EThe%20formula%20has%20to%20consider%20the%20four%20roles%20that%20for%20define%20the%20results%20for%20each%20cell%20in%20the%20column%20%22Value%22.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDoes%20someone%20has%20a%20solution%20to%20solve%20this%20situation%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2713960%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2714018%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20parameters%20for%20formula%20IF%20(with%20different%20cells)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2714018%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1145046%22%20target%3D%22_blank%22%3E%40Prevenda_XPTA%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(AND(A2%3D%22Company%2001%22%2CB2%3D%22Project%22)%2C120%2CIF(AND(A2%3D%22Company%2001%22%2CB2%3D%22Presales%22)%2C90%2CIF(AND(A2%3D%22Company%2002%22%2CB2%3D%22Project%22)%2C150%2CIF(AND(A2%3D%22Company%2002%22%2CB2%3D%22Presales%22)%2C115))))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%3E%3DIF(%0A%20%20%20%20AND(%0A%20%20%20%20%20%20%20%20A2%20%3D%20%22Company%2001%22%0A%20%20%20%20)%2C%0A%7B%20B2%20%3D%20%22Project%22%0A%20%20%20%20)%0A)%2C%0A%7B%20120%0A)%2C%0A%7B%20%20%20%20IF(%0A%20%20%20%20%20%20%20%20AND(%0A%20%20%20%20%20%20%20%20%20%20%20%20A2%20%3D%20%22Company%2001%22%0A%20%20%20%20%20%20%20%20)%2C%0A%7B%20B2%20%3D%20%22Presales%22%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20)%2C%0A%7B%2090%0A%20%20%20%20)%2C%0A%7B%20%20%20%20%20%20%20%20IF(%0A%20%20%20%20%20%20%20%20%20%20%20%20AND(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20A2%20%3D%20%22Company%2002%22%0A%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%7B%20B2%20%3D%20%22Project%22%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20)%2C%0A%7B%20150%0A%20%20%20%20%20%20%20%20)%2C%0A%7B%20%20%20%20%20%20%20%20%20%20%20%20IF(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20AND(%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20A2%20%3D%20%22Company%2002%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%7B%20B2%20%3D%20%22Presales%22%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20%20%20%20%20)%2C%0A%7B%20115%0A%20%20%20%20%20%20%20%20%20%20%20%20)%0A%20%20%20%20%20%20%20%20)%0A%20%20%20%20)%0A)%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2715410%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20parameters%20for%20formula%20IF%20(with%20different%20cells)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2715410%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1145046%22%20target%3D%22_blank%22%3E%40Prevenda_XPTA%3C%2FA%3E%26nbsp%3BAs%20a%20variant%2C%20create%20a%20lookup%20table%20with%20the%20parameters%20and%20use%20one%20of%20several%20possible%20functions%20to%20find%20a%20value%20based%20on%20the%20company%20name%20and%20description.%20The%20attached%20workbook%20contains%20three%20examples%20using%20XLOOKUP%2C%20SUMIFS%20and%20SUMPRODUCT.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENote%20that%20I%20have%20used%20named%20ranges%20to%20make%20the%20formulae%20easier%20to%20read.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2716745%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20parameters%20for%20formula%20IF%20(with%20different%20cells)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2716745%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F767933%22%20target%3D%22_blank%22%3E%40Juliano-Petrukio%3C%2FA%3E%2C%20thank%20you%20for%20your%20help%2C%20I%20really%20appreciated!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2716757%22%20slang%3D%22en-US%22%3ERe%3A%20Multiple%20parameters%20for%20formula%20IF%20(with%20different%20cells)%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2716757%22%20slang%3D%22en-US%22%3ERiny%2C%3CBR%20%2F%3E%3CBR%20%2F%3EThank%20you%20for%20your%20help%2C%20it%20a%20interesting%20way%20to%20solve%20the%20problem.%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

Hello,

 

I'm trying to compose a formula using "IF", considering the complexity of using parameters informed in  two columns to defining the result in a third column(if true)  based on the selection of information between the two columns before.

See the  example  bellow:

Column 1                Column 2           Value (result)

Company 1              Project                      $ 120,00 

Company 1              Presales                    $   90,00

Company 2              Project                      $ 150,00

Company 2              Presales                    $ 115,00

I'm trying to use the four lines to configure a formule to set the "value" automatically, according to the options selected, for example:

 If the information selected in the column 1 is company 1 and de information selected in the column 2 is Presales, the value has to be set automatically to $90,00.

 If the information selected in the column 1 is company 2 and de information selected in the column 2 is Presales, the value has to be set automatically to $115,00.

The formula has to consider the four roles that for define the results for each cell in the column "Value".

 

Does someone has a solution to solve this situation?

 

Thank you.

5 Replies
best response confirmed by allyreckerman (Microsoft)
Solution

@Prevenda_XPTA 

=IF(AND(A2="Company 01",B2="Project"),120,IF(AND(A2="Company 01",B2="Presales"),90,IF(AND(A2="Company 02",B2="Project"),150,IF(AND(A2="Company 02",B2="Presales"),115))))

 

 

 

=IF(
    AND(
        A2 = "Company 01"
    ),
{ B2 = "Project"
    )
),
{ 120
),
{    IF(
        AND(
            A2 = "Company 01"
        ),
{ B2 = "Presales"
        )
    ),
{ 90
    ),
{        IF(
            AND(
                A2 = "Company 02"
            ),
{ B2 = "Project"
            )
        ),
{ 150
        ),
{            IF(
                AND(
                    A2 = "Company 02"
                ),
{ B2 = "Presales"
                )
            ),
{ 115
            )
        )
    )
)

 

@Prevenda_XPTA As a variant, create a lookup table with the parameters and use one of several possible functions to find a value based on the company name and description. The attached workbook contains three examples using XLOOKUP, SUMIFS and SUMPRODUCT.

 

Note that I have used named ranges to make the formulae easier to read.

@Juliano-Petrukio, thank you for your help, I really appreciated!

Riny,

Thank you for your help, it a interesting way to solve the problem.

@Prevenda_XPTA 

Some 365 variants with the data built into the formula.

= LET(
    V,{120,90;150,115},
    C, XMATCH(Company,{"Company 1";"Company 2"}),
    D, XMATCH(Description,{"Project";"Presales"}),
    INDEX(V,C,D)
  )

with the values held as a 2x2 array.

The next step could be to hide all the 'nuts and bolts' within a Lambda function 'VALUEλ'

= LAMBDA(Co,Dn,
    LET(
      V,{120,90;150,115},
      C, XMATCH(Co,{"Company 1";"Company 2"}),
      D, XMATCH(Dn,{"Project";"Presales"}),
      INDEX(V,C,D)
    )
  )

so that the final worksheet formula reads

= VALUEλ(Company,Description)

Like the first two of @Riny_van_Eekelen's formulas, it can be applied to an entire list of companies and value descriptions as a single dynamic array.