Forum Discussion

Prevenda_XPTA's avatar
Prevenda_XPTA
Copper Contributor
Sep 02, 2021
Solved

Multiple parameters for formula IF (with different cells)

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.

Formulas and Functions
  • Juliano-Petrukio's avatar
    Juliano-Petrukio
    Sep 02, 2021

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

     

5 Replies

  • PeterBartholomew1's avatar
    PeterBartholomew1
    Silver Contributor
    Sep 03, 2021

    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.

  • Riny_van_Eekelen's avatar
    Riny_van_Eekelen
    Platinum Contributor
    Sep 03, 2021

    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.

    • Prevenda_XPTA's avatar
      Prevenda_XPTA
      Copper Contributor
      Sep 03, 2021
      Riny,

      Thank you for your help, it a interesting way to solve the problem.
  • Juliano-Petrukio's avatar
    Juliano-Petrukio
    Bronze Contributor
    Sep 02, 2021

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

     

Resources