conditional calculation based on values from another column

Question

I have 2 tables

Table 1

Table 2

They are connected by pkg and Item. This results in the following

As you can see from the image "Item" has a mix of "BULK" & "PKG" values.

I want to create a new column that uses the cross reference to bring in new values.

The new table I want would look like this

 **ITEM**  **Value**   **BULK**  **PKG**   **NEW COLUMN**
  135          2                                 1
  136          4                                 3
  35           1          135       35           1
  36           3          136       36           3

The top 2 items on the left are the associated "BULK" values. I want to bring in the PKG value that appears.

Alexis Olson · Accepted Answer · 2019-05-31 18:30:40Z

2

You can use the same ISBLANK conditional expression as in your other question, but use a lookup instead of just Value.

New Column =
IF(
    ISBLANK( Table1[Bulk] ),
    LOOKUPVALUE( Table1[Value], Table1[BULK], Table1[ITEM] )
    Table1[Value]
)

Note that this will throw an error if you have multiple BULK values corresponding to a single ITEM.

answered May 31, 2019 at 18:30

Alexis Olson

40.4k8 gold badges51 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

conditional calculation based on values from another column

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related