Forum Discussion

eburg's avatar
eburg
Frequent Visitor
4 years ago
Solved

How to return value in a column based on two criteria?

Hello, 

 

I have what I think is a fairly straightforward question that I can't quite execute. I have criteria, in this case ingredient, that are shared across multiple items. I want to look up and return a value based on multiple criteria into another dataset.

 

So I have a dataset that has days worth of results with both Column A and Column C below. So the Ingredient 1 and Ingredient 2 info. I want to pull into that sheet the results from Column E "Output" below.

So when the info in my dataset equals "929062" and "926664" it returns 0.01. And then when it equals "929429" and "926664" it will return whatever is in column E "Output" even though the "92664" is shared between different values in Column A. In this case they're the same but typically they are different (if that helps).

 

  • Hi, eburg 

     

    You can try the following methods.

    Column = 
    IF (
        [Ingredient 1] = 929062
            && [Ingredient 2] = 926664,
        0.01,
        IF (
            [Ingredient 1] = 929429
                && [Ingredient 2] = 926664,
            MAX ( 'Table'[Output] ),
            BLANK ()
        )
    )

    If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • Hi eburg 

     

    Try this to add a new column:

    Column=
    IF([Ingredient 1]=929062&&[Ingredient 2]=926664,0.01,[Output])

    If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudos!!
    LinkedIn: 
    www.linkedin.com/in/vahid-dm/

     

     

     

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support

    Hi, eburg 

     

    You can try the following methods.

    Column = 
    IF (
        [Ingredient 1] = 929062
            && [Ingredient 2] = 926664,
        0.01,
        IF (
            [Ingredient 1] = 929429
                && [Ingredient 2] = 926664,
            MAX ( 'Table'[Output] ),
            BLANK ()
        )
    )

    If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

     

    Best Regards,

    Community Support Team _Charlotte

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.