Translate VLOOKUP to SQL Joins

Context

Financial analysts at Relias often move from Excel-based lookup workflows into SQL-based reporting. A strong answer should show that you understand both what VLOOKUP does and how to reproduce that logic in PostgreSQL.

Core question

Walk through a VLOOKUP function and explain how you would translate it into SQL. In your answer, cover:

1. What VLOOKUP is doing conceptually
2. Which SQL operation most closely matches it
3. How to handle exact matches and missing matches
4. Why SQL is often more flexible than VLOOKUP for finance reporting in Relias

Scope guidance

Keep the explanation practical rather than academic. The interviewer is looking for a clear mapping from spreadsheet logic to SQL joins, plus awareness of common issues like unmatched keys, duplicate matches, and returning a default value when no match exists.

VLOOKUP as key-based matching

VLOOKUP searches for a value in one dataset and returns a related value from another column in the same lookup range. In SQL terms, this is a key-based relationship between rows, usually using a shared identifier such as account code, invoice ID, or facility ID.

SELECT t.account_code, m.account_name
FROM transactions t
LEFT JOIN account_mapping m
  ON t.account_code = m.account_code;

LEFT JOIN is the closest SQL equivalent

A LEFT JOIN returns every row from the main table and brings in matching values from the lookup table when they exist. That mirrors the common VLOOKUP use case where you want to preserve all source rows and enrich them with reference data.

SELECT t.invoice_id, t.account_code, m.account_name
FROM relias_billing_transactions t
LEFT JOIN relias_account_mapping m
  ON t.account_code = m.account_code;

Handling missing matches

When VLOOKUP cannot find a match, Excel often returns an error like #N/A. In SQL, unmatched rows from a LEFT JOIN appear as NULL, and you can replace those NULL values with a fallback label using COALESCE or CASE WHEN.

SELECT t.account_code,
       COALESCE(m.account_name, 'Unmapped Account') AS account_name
FROM relias_billing_transactions t
LEFT JOIN relias_account_mapping m
  ON t.account_code = m.account_code;

Exact match vs approximate match

Most analyst use cases rely on exact matching, which corresponds to joining on equality in SQL. Approximate-match VLOOKUP behavior is less common in SQL interviews and usually requires additional logic, such as range joins or ordered matching, rather than a simple equality join.

SELECT t.facility_id, f.region_name
FROM relias_training_revenue t
LEFT JOIN relias_facility_dimension f
  ON t.facility_id = f.facility_id;

Duplicate keys change the result

VLOOKUP typically returns the first matching value it encounters, which can hide duplicate-key problems. In SQL, duplicate keys in the lookup table can create multiple output rows, so analysts should understand data quality and uniqueness assumptions before joining.