What Function To Use With Multiple Values
I am trying to make a sheet that allows me to select an item (product) from a dropdown menu and then auto-populates a unit price for that specific product. I have 16 different products listed and different prices for each product. I have tried the =IF(CONTAINS() function and =IF(OR() function. The =IF(OR() function worked until I got past two different prices and then it gave me an "INCORRECT ARGUMENT SET" error message.
=IF(OR(Description@row = "100 - Ambrosia Apple (1)", Description@row = "101 - Mt Everest Crab Apple (1)", Description@row = "102 - Coral Champagne Cherry (1)", Description@row = "103 - Chelan Cherry (1)", Description@row = "104 - Duarte Plum (1)", Description@row = "105 - Red Gold Nectarine (1)"), "25", IF(OR(Description@row = "106 - Mt. Huckleberry (2)", Description@row = "107 - Taper Leaf Penstemon (2)", Description@row = "108 - Showy Milkweed (2)", Description@row = "109 - Purple Coneflower (2)", Description@row = "110 - Woods Rose (2)", Description@row = "111 - Shaggy Fleabane (2)"), "10", IF(OR(Description@row = "112 - Quaking Aspen (5)", Description@row = "114 - Ponderosa Pine (10)"), "20", IF(Description@row = "113 - Western Larch (5)"), "12", IF(Description@row = "115 - Coffee Burlap bags (1)"), "2", IF(Description@row = "116 - Tree tubes (1)"), "1.75")))
The =IF() function gave me the "UNPARSEABLE" message.
=IF([Description]@row = "100 - Ambrosia Apple (1)", [Description]@row = "101 - Mt Everest Crab Apple (1)", [Description]@row = "102 - Coral Champagne Cherry (1)", [Description]@row = "103 - Chelan Cherry (1)", [Description]@row = "104 - Duarte Plum (1)", [Description]@row = "105 - Red Gold Nectarine (1)", "25"), IF([Description]@row = "106 - Mt. Huckleberry (2)", [Description]@row = "107 - Taper Leaf Penstemon (2)", [Description]@row = "108 - Showy Milkweed (2)", [Description]@row = "109 - Purple Coneflower (2)", [Description]@row = "110 - Woods Rose (2)", [Description]@row = "111 - Shaggy Fleabane (2)", "10"), IF([Description]@row = "112 - Quaking Aspen (5)", [Description]@row = "114 - Ponderosa Pine (10)", "20"), IF([Description]@row = "113 - Western Larch (5)", "12"), IF([Description]@row = "115 - Coffee Burlap bags (1)", "2"), IF([Description]@row = "116 - Tree tubes (1)", "1.75")
Answers
-
Try creating a table that has each product listed once going down a column and their prices going down another column. Then you can use an INDEX/MATCH to pull in the price like so:
=INDEX({Price Column}, MATCH(Description@row, {Product Column}, 0))
-
I hope you're well and safe!
Can you list the different products and prices?
Have a fantastic week & Happy New Year!
Best,
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 441 Global Discussions
- 139 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 496 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!