Lookup multiple values from a dropdown cell and SUM
Hello,
I am trying to populate Total Points from Sheet 1 with a formula.
I would need to do a multivalue vlookup for my Sheet1[Color] and grab the related points from Sheet2[Color] and SUM them into my Sheet1[Total Points]. Is there a way to do this in Smartsheet?
Please see attached images
Thanks in advance
Answers

Hi ConfidentCoffee,
Try below solution. Add more helper columns to seperate colors then sum them all.
Hope it works for you.
Gia Thinh Technology  Smartsheet Solution Partner.

Hello,
Thanks for your quick response to the question, however, the table provided is a sample data to reflect my real data. In the real data, there are "Color" column in SHEET 1 for every day of the month from 131. In the example below, I have only created up to 1/6/2023 but lets pretend it goes all the way until 1/31/2023:
It does not seem efficient to put all 5 colors as a column * 31 days (155 columns), is there another way to approach this?

We can combine the formula as below and remove the color columns
=SUM(VLOOKUP(IF(CONTAINS("Red"; Color@row); "Red"); {Sheet 2 Range 1}; 2; 0); VLOOKUP(IF(CONTAINS("Orange"; Color@row); "Orange"); {Sheet 2 Range 1}; 2; 0); VLOOKUP(IF(CONTAINS("Purple"; Color@row); "Purple"); {Sheet 2 Range 1}; 2; 0); VLOOKUP(IF(CONTAINS("Yellow"; Color@row); "Yellow"); {Sheet 2 Range 1}; 2; 0); VLOOKUP(IF(CONTAINS("Black"; Color@row); "Black"); {Sheet 2 Range 1}; 2; 0); VLOOKUP(IF(CONTAINS("Green"; Color@row); "Green"); {Sheet 2 Range 1}; 2; 0))
Gia Thinh Technology  Smartsheet Solution Partner.
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.1K Get Help
 380 Global Discussions
 212 Industry Talk
 444 Announcements
 4.6K Ideas & Feature Requests
 140 Brandfolder
 129 Just for fun
 130 Community Job Board
 450 Show & Tell
 30 Member Spotlight
 1 SmartStories
 291 Events
 35 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!