Formula to lookup a value based on criteria in 2 different columns?

Alyssa

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.

L_123

=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.