Formula to lookup a value based on criteria in 2 different columns?
I am looking for some help with a formula on my Roll-up sheet. I want to pull the Score by Site for the different years based on the information populated in my Data Sheet.
Criteria #1: Site Name (e.g. Chandler, Chicago)
Criteria #2: Year (e.g. "2018", "2019", "2020")
Data sheet:
Roll-up sheet example (where I'll plug in the formulas to be able to look up the Site's 2018 score, 2019 score, etc.
I completed the Weighted score with a =vlookup, but I am having trouble factoring in the 2 criteria for a lookup formula.
Best Answer
-
=join(collect(Score:Score,[Site Name]:[Site Name],"Chandler",assessment:assessment,"2018"),",")
You can give that a try. Of course you can change what I have as column references to other sheet references and the name/date to a cell reference in order to drag this down and autocomplete the formula for different criteria.
If multiple items are found meeting the criteria with this formula it will put a comma between them and post all of them in a single cell. You can use this for troubleshooting, if you see multiple items in the return you know something was entered in duplicate on the other sheet.
Answers
-
=join(collect(Score:Score,[Site Name]:[Site Name],"Chandler",assessment:assessment,"2018"),",")
You can give that a try. Of course you can change what I have as column references to other sheet references and the name/date to a cell reference in order to drag this down and autocomplete the formula for different criteria.
If multiple items are found meeting the criteria with this formula it will put a comma between them and post all of them in a single cell. You can use this for troubleshooting, if you see multiple items in the return you know something was entered in duplicate on the other sheet.
-
Thanks for your help! I was having a difficult time until I realized that the formula won't read embedded hyperlinks... :)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!