Returning a value based on one or more of 3 critieria

Options

Hi,

I am trying to setup a formula that will return a product description if the entered value matches one of three values. The inventory system we have uses tolling numbers, as well as bulk and final good numbers for identification. Here is an example of the sheet I would be pulling the description from:

As long as the user entered value matches one of the Bulk, Tolling or final Good, I would like the description to be returned in the Product Description field next to the user entered value.

Typically I would use VLOOKUP to pull from another sheet, but I am not having any luck determining how to set this formula up.

Thanks,

Tom

Tags:

Best Answer

  • Hollie205
    Hollie205 ✭✭✭
    Answer ✓
    Options

    I would do an Index collect with if errors

    =IFERROR(INDEX(COLLECT({Product Description Reference},{Bulk # Reference},[Product #]@row),1),"")+IFERROR(INDEX(COLLECT({Product Description Reference},{Tolling # Reference},[Product #]@row),1),"")+IFERROR(INDEX(COLLECT({Product Description Reference},{Final Good # Reference},[Product #]@row),1),"")

Answers

  • Eric Law
    Eric Law ✭✭✭✭✭✭
    Options

    hello @tmckivergan you will need if statements with either match or vlookup.

    =IF(CONTAINS(XXX@row, {Bulk #}), VLookup(XXX@row, {Inventory Table 1}, 4, 0), IF(CONTAINS(.... so on for each type.

    Hope that helps get you started.

  • tmckivergan
    tmckivergan ✭✭✭
    Options

    Thank you. I have this but it is giving #UNPARSEABLE:

    =IF(CONTAINS([Product #]@row, {Master Item Listing Range 2}), VLOOKUP([Product #]@row, {Master Item Listing Range 6}, 2, false)), IF(CONTAINS([Product #]@row,{Master Item Listing Range 4}),VLOOKUP([Product #]@row, {Master Item Listing Range 3}, 2, false)), IF(CONTAINS([Product #]@row,{Master Item Listing Range 5}),VLOOKUP([Product #]@row, {Master Item Listing Range 1}, 2, false))

  • tmckivergan
    tmckivergan ✭✭✭
    Options

    Actually I am really close now. I have this that can work for the two criteria:

    =IF(CONTAINS([Product #]@row, {Master Item Listing Range 2}), VLOOKUP([Product #]@row, {Master Item Listing Range 6}, 2, false), VLOOKUP([Product #]@row, {Master Item Listing Range 3}, 2, false))

    My issue is tagging on the last possible product number into the formula

  • tmckivergan
    tmckivergan ✭✭✭
    Options

    OK I have a formula that is valid, but it is not pulling the references that I need:

    =IF(CONTAINS([Product #]@row, {Master Item Listing Range 2}), VLOOKUP([Product #]@row, {Master Item Listing Range 6}, 2, false), IF(CONTAINS([Product #]@row, {Master Item Listing Range 5}), VLOOKUP([Product #]@row, {Master Item Listing Range 1}, 2, false), IF(CONTAINS([Product #]@row, {Master Item Listing Range 4}), VLOOKUP([Product #]@row, {Master Item Listing Range 3}, 2, false), "N/A")))

    I have a feeling my nested IF statements are not 100% correct, but I can't pinpoint it. Any thoughts?

  • Hollie205
    Hollie205 ✭✭✭
    Answer ✓
    Options

    I would do an Index collect with if errors

    =IFERROR(INDEX(COLLECT({Product Description Reference},{Bulk # Reference},[Product #]@row),1),"")+IFERROR(INDEX(COLLECT({Product Description Reference},{Tolling # Reference},[Product #]@row),1),"")+IFERROR(INDEX(COLLECT({Product Description Reference},{Final Good # Reference},[Product #]@row),1),"")

  • tmckivergan
    tmckivergan ✭✭✭
    Options

    That did it. Thank you!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!