INDEX and MATCH can retrieve a value from a column to the left of a lookup key. The two ranges must describe the same sequence of records.
Separate locating from returning
Suppose names are in A2:A100 and unique IDs are in C2:C100. With the requested ID in E2, use =INDEX(A2:A100,MATCH(E2,C2:C100,0)).
MATCH returns the key’s position within C2:C100; INDEX uses that position within A2:A100. The 0 requests exact matching, so the key column does not need to be sorted for an approximate search.
Align the record boundaries
Keep the return and lookup ranges on corresponding rows. Starting one at row 1 and the other at row 2 shifts the relationship. Test the first and last possible records to catch this mistake.
When copying the formula, lock the source ranges as appropriate and allow the requested-ID cell to move. MATCH expects a one-dimensional lookup range, so do not supply an entire multicolumn table as its search range.
Verify missing and repeated keys
Try one known ID and one absent ID. If absence is expected, add a clearly explained IFNA result after the underlying lookup works. Preserve other errors instead of silently converting every failure into a blank.
Check for duplicate IDs before treating the returned name as unique. If several records legitimately share a key, define a selection or aggregation policy. This method removes a left-to-right lookup constraint; it does not solve conflicting source records or establish the freshness of the returned attribute. Keep the source data and the matching policy visible to maintainers.
References
Reviewed against the linked official product guidance on November 3, 2026. Features, limits, screen labels, and access can vary by account, platform, region, and organization policy.