Consolidating Risk Scores in Excel | Dataford Interview Questions

Explain how you would structure a VLOOKUP or INDEX/MATCH formula to consolidate risk scores from multiple departments for Uber.

For SQL grading, treat each assessment as the lookup key and return the latest available score for each required department. Preserve assessments even when a department has no score.

Output

One row per assessment, with assessment_id, assessment_date, operations_score, finance_score, safety_score, and compliance_score.
Use NULL when a departmental score is unavailable.
Order by assessment_date ascending, then assessment_id ascending.

Schema

assessments

Column	Type	Description
assessment_idPK	INT	Unique assessment identifier
assessment_date	DATE	Date of the assessment

departments

Column	Type	Description
department_idPK	INT	Unique department identifier
department_name	VARCHAR(50)	Department name used for consolidation

risk_scores

Column	Type	Description
risk_score_idPK	INT	Unique risk score record identifier
assessment_id	INT	Assessment receiving the score
department_id	INT	Department providing the score
risk_score	INT	Department risk score
recorded_at	TIMESTAMP	Time the score was recorded

Your question is Consolidating Risk Scores in Excel. Start with the requirements and the three tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem