Lookup multiple values from a dropdown cell and SUM

ConfidentCoffee

Hello,

I am trying to populate Total Points from Sheet 1 with a formula.

I would need to do a multi-value 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

Gia Thinh

Hi ConfidentCoffee,

Try below solution. Add more helper columns to seperate colors then sum them all.

Hope it works for you.

ConfidentCoffee

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 1-31. 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?

Gia Thinh

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