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
- 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
- 494 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!