# Lookup multiple values from a dropdown cell and SUM

Options

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?

• ✭✭✭✭✭✭
edited 12/28/23
Options

Hi ConfidentCoffee,

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

Hope it works for you.

Gia Thinh Technology Co., LTD - Smartsheet Solution Partner.

• Options

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?

• ✭✭✭✭✭✭
Options

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 Co., LTD - Smartsheet Solution Partner.

## Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!