Sep 02 2021 03:49 PM
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.
Sep 02 2021 04:11 PM - edited Sep 02 2021 04:13 PM
Solution=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
)
)
)
)
Sep 02 2021 10:21 PM
@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.
Sep 03 2021 04:46 AM
@Juliano-Petrukio, thank you for your help, I really appreciated!
Sep 03 2021 04:53 AM
Sep 03 2021 08:23 AM
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.
Sep 02 2021 04:11 PM - edited Sep 02 2021 04:13 PM
Solution=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
)
)
)
)