Returning a value based on one or more of 3 critieria

tmckivergan

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

Hollie205

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

Eric Law

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

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

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

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

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

That did it. Thank you!