# 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:

• ✭✭✭
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),"")

• ✭✭✭✭✭✭
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.

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

• ✭✭✭
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

• ✭✭✭
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?

• ✭✭✭
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),"")

• ✭✭✭
Options

That did it. Thank you!

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!