Context

Spreadsheet users often rely on INDEX-MATCH and VLOOKUP to pull values from reference tables. In SQL interviews, this usually maps to understanding how lookup logic translates into table design and query patterns.

Core question

Explain how INDEX-MATCH works in spreadsheets, why many analysts prefer it over VLOOKUP, and how the same problem is typically solved in SQL. Your answer should cover:

1. What INDEX and MATCH each do
2. Why INDEX-MATCH is more flexible than VLOOKUP
3. What limitations VLOOKUP has
4. How SQL replaces spreadsheet lookups using keys and joins

Scope guidance

The interviewer is looking for a practical conceptual explanation, not spreadsheet syntax memorization. Focus on lookup direction, column-position dependence, maintainability, and how SQL joins are the database equivalent of robust lookup logic.

INDEX returns a value by position

INDEX returns the value from a specified row and column position within a range. By itself, it is positional rather than search-based, so it is usually paired with MATCH to first find the correct position.

MATCH finds the lookup position

MATCH searches a range for a value and returns its relative position. Combined with INDEX, it allows you to search one column and return a value from another column without hard-coding a column number.

VLOOKUP depends on table layout

VLOOKUP searches the first column of a range and returns a value from a fixed column offset to the right. This makes it less flexible because inserting, removing, or reordering columns can break the logic or return the wrong result.

SQL uses joins instead of positional lookup

In SQL, lookup problems are usually solved by joining tables on a key such as customer_id or product_sku. This is more explicit and maintainable because the relationship is based on column names and keys, not physical column order.

SELECT s.sale_id, p.product_name
FROM sales s
LEFT JOIN products p
  ON s.product_sku = p.product_sku;

Keys matter more than positions

A strong SQL answer emphasizes primary keys, foreign keys, and unique identifiers. Unlike spreadsheet formulas that often depend on cell ranges and positions, SQL is designed around relational matching using stable keys.

SELECT e.employee_id, d.department_name
FROM employees e
JOIN departments d
  ON e.department_id = d.department_id;