How does the SQL version of INDEX MATCH work?

MATCH(A2, ..., 0) finds the row whose key equals the order's customer id; the WHERE clause in the subquery does the same. INDEX then returns the chosen column from that row, which is the SELECT list of the subquery. LIMIT 1 mirrors INDEX MATCH returning only the first match and avoids an error if the key is duplicated.

Why can SQL give a different result from INDEX MATCH?

A join (see VLOOKUP) is usually clearer and faster in SQL; the subquery form is shown because it mirrors the formula.

INDEX MATCH in SQL

INDEX MATCH looks up a value by finding its position in one column and returning the item at that position in another; SQL does it with a join or a correlated subquery.

The Excel version

=INDEX(Customers!C:C, MATCH(A2, Customers!A:A, 0))

The SQL equivalent

This example assumes: orders(id bigint, customer_id bigint, total_amount numeric), customers(id bigint, name text, country text). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT o.id,
       o.total_amount,
       (SELECT c.country
        FROM customers c
        WHERE c.id = o.customer_id
        LIMIT 1) AS country
FROM orders o
ORDER BY o.id;

How it works

MATCH(A2, ..., 0) finds the row whose key equals the order's customer id; the WHERE clause in the subquery does the same.
INDEX then returns the chosen column from that row, which is the SELECT list of the subquery.
LIMIT 1 mirrors INDEX MATCH returning only the first match and avoids an error if the key is duplicated.

Where the results differ from Excel

A join (see VLOOKUP) is usually clearer and faster in SQL; the subquery form is shown because it mirrors the formula.
If the key is not found, MATCH gives #N/A where the subquery returns null.
LIMIT 1 without ORDER BY picks an arbitrary match when keys repeat; add an ORDER BY to make it deterministic.

Skip the query: ask in plain English

If you would rather not write SQL, type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the result matches how your spreadsheet defines the number.

Look up each order's customer country.
Which orders point at a customer id that does not exist?