INDEX and MATCH lookup to the left

How it works, step by step

1. 01E2 holds the Employee ID you want to look up, for example 10457.
2. 02MATCH(E2,$B$2:$B$500,0) searches the ID column for that exact value and returns its position in the range, say 37. The 0 means exact match.
3. 03INDEX($A$2:$A$500,37) returns the 37th item in the name column, which is the name on the same row as the ID.
4. 04The return range and the lookup range are separate arguments, so the name column can sit to the left of the ID column. VLOOKUP cannot do that.

The same logic in four tools

=INDEX($A$2:$A$500,MATCH(E2,$B$2:$B$500,0))

=INDEX($A$2:$A$500,MATCH(E2,$B$2:$B$500,0))

SELECT t.employee_id, e.employee_name
FROM timesheet t
LEFT JOIN employees e ON e.employee_id = t.employee_id;

import pandas as pd

result = timesheet.merge(
    employees[["employee_id", "employee_name"]],
    on="employee_id",
    how="left",
)

Mistakes that break it

• !Both ranges must start and end on the same rows. Pairing $A$2:$A$500 with $B$1:$B$499 returns the name from the row above, with no error.
• !Leaving off the 0 in MATCH defaults to 1 (approximate match on sorted data), which returns wrong names on an unsorted ID list.
• !MATCH returns the first match only. Duplicate IDs in column B always resolve to the top occurrence.
• !IDs typed as numbers in column E will not match IDs stored as text in column B (the cells with a green triangle). Convert one side so both are the same type.

Common questions

Is INDEX MATCH better than VLOOKUP?

INDEX/MATCH can return columns to the left of the lookup column and does not break when columns are inserted, because it points at the return column directly. In Excel 365 and Google Sheets, XLOOKUP does the same job in one function.

What does the 0 in MATCH mean?

It sets match_type to exact match. A 1 finds the largest value less than or equal to the lookup value in ascending data, and -1 finds the smallest value greater than or equal to it in descending data.

Why does INDEX MATCH return the wrong row?

The INDEX range and the MATCH range are probably offset, for example one starts on row 1 and the other on row 2. Make both ranges start and end on the same rows.