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 selectio...

Formulas and Functions

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

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.xlsx10 KB

Prevenda_XPTA

Copper Contributor

Sep 03, 2021

Riny,

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

Forum Discussion

Multiple parameters for formula IF (with different cells)

Resources